Hi all,
Running into an issue when trying to do multiple levels of aggregation in a beastmode calculation (not sure if it is actually possible as a beastmode).
I have a dataset that looks something like the below with data at the item-location-day level and a servicing DC associated with each store. What I am attempting to do is two levels of aggregation within a card.
| Date | DC | Store | Item_# | proactive_need | reactive_need | scip_alloc_units | rtrp_alloc_units |
| 28-Nov | 1 | 15 | 55742 | 10 | 0 | 8 | 0 |
| 28-Nov | 1 | 15 | 55742 | 15 | 2 | 10 | 2 |
| 28-Nov | 1 | 15 | 55742 | 5 | 0 | 8 | 0 |
| 28-Nov | 2 | 44 | 35897 | 7 | 3 | 4 | 0 |
| 28-Nov | 2 | 44 | 35897 | 9 | 0 | 12 | 0 |
| 28-Nov | 2 | 44 | 35897 | 4 | 4 | 0 | 0 |
I want to first create a calculated field which uses the item-loc-day level data to determine if a certain suite of logic was used for that item which can only be determined by rolling up the data to the item-DC level (as this is the level where "shortage" logic is determined. The calculation I have used to do this is:
(CASE when SUM((case when ifnull(`rtrp_alloc_units`,0)>ifnull(`reactive_need`,0) then ifnull(`reactive_need`,0) else ifnull(`rtrp_alloc_units`,0) end) + (case when ifnull(`scip_alloc_units`,0)>ifnull(`proactive_need`,0) then ifnull(`proactive_need`,0) else ifnull(`scip_alloc_units`,0) end))>0 and SUM((case when ifnull(`rtrp_alloc_units`,0)>ifnull(`reactive_need`,0) then ifnull(`reactive_need`,0) else ifnull(`rtrp_alloc_units`,0) end) + (case when ifnull(`scip_alloc_units`,0)>ifnull(`proactive_need`,0) then ifnull(`proactive_need`,0) else ifnull(`scip_alloc_units`,0) end))<SUM(`proactive_need`+`reactive_need`) then 1 else 0 end)
| Item | DC | Sum of proactive_need | Sum of reactive_need | Sum of scip_alloc_units | Sum of rtrp_alloc_units | Shortage |
| 55742 | 1 | 30 | 2 | 26 | 2 | 1 |
| 35897 | 2 | 20 | 7 | 16 | 0 | 1 |
Somewhat complicated but essentially is looks at if a certain value is > 0 but below another value at the item DC level and if so then it is shortage. This is the level we want to be able to calculate this at, but not the level we want to visualize at because there are many item-dc combinations and there would be hundreds of thousands of rows.
The idea would be to then sum the number of item-dcs with a 1 in the shortage column, but since the calculated field is contructed in such a way as to apply to the item-dc level already and not item-store which is the true level of this data, the calculated field will just apply to the total DC level and do all of the aggregations there instead of doing the aggregation at item-dc and then aggregating based on that aggregation.
Is there a way to work around this in a beastmode, or if not is there an easy way to do something like this in ETL where I could pre-aggregate to the item-DC level and then do further aggregations in the card itself.
Thanks,
Adam
