Nested case function (average over time period)

user02674
user02674 Member
in Archive

Hello,

 

I am trying to build a card that has dynamic goals incorporated into it based on the 30 day history. 

The below is what I currently have set up as the goals based on the mdse_grp_ref_i which allow it to roll up to higher level goals based on the mix of items that fall into each grp.

 

(case
when `mdse_grp_ref_i` = '1' then 8.46
when `mdse_grp_ref_i` = '13' then 7.41
when `mdse_grp_ref_i` = '10' then 9.27
when `mdse_grp_ref_i` = '3' then 10.20
when `mdse_grp_ref_i` = '4' then 11.35
else 0
end)

 

What I would like to get to is a state where I can plug in a formula (already created in a separate Beast Mode Calc) after each "then" in the above statement and also have this formula take an average of the previous 30 days. I am not sure if this is possible. I wrote the below code as a rough attempt, but am having trouble getting it to work. For starters I know DOMO isn't a fan of having sum or avg functions within the case statement but am not sure how to pull it out and still have the calculation work. At the same time I am unsure of how to have the portion of the code below, which calculates the percentage of item/locations that started with less inventory that they should, average over the past 30 days.

 

(case
when `mdse_grp_ref_i` = '1' then (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)
when `mdse_grp_ref_i` = '13' then (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)
when `mdse_grp_ref_i` = '10' then (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)
when `mdse_grp_ref_i` = '3' then (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)
when `mdse_grp_ref_i` = '4' then (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)
else 0
end)

 

Any help you can provide will be much appreciated.

 

Thanks,

Adam

Comments

  • PodiumMason
    PodiumMason Contributor

    Hey Adam @user02674,

     

    From the looks of the nested beast mode, as you assessed, it doesn't like you putting aggregate functions into what it's seeing as a row level function. This is probably why the code isn't functional.

     

    Have you considered using ETL or Dataflow to hard code the values in these segments into their own column? (0.6527 * (avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end))+0.0189)

     

    That way your dataset has the column value to multiply by already in the data, so at that point it's just a matter of simple one part case statements. 

     

    Hope this is helpful. 

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • user00066
    user00066 Member

    try adding max after avg function

     

    (0.6527 * (max(avg(case when `loc_type` = 'STR' and (`boh_8am` + `str_ow_8am_q`) < `int_new1` then 1 else 0 end) * 100 / sum(case when `loc_type` = 'STR' then 1 else 0 end)))+0.0189)

Categories