Multiple Level Aggregation

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. 

 

DateDCStoreItem_#proactive_needreactive_needscip_alloc_unitsrtrp_alloc_units
28-Nov1155574210080
28-Nov11555742152102
28-Nov115557425080
28-Nov244358977340
28-Nov2443589790120
28-Nov244358974400

 

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)

 

ItemDCSum of proactive_needSum of reactive_needSum of scip_alloc_unitsSum of rtrp_alloc_unitsShortage
5574213022621
3589722071601

 

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

Comments

  • Hi There, 

    If whenever I need to pre-aggregate especially on multiple columns I find writing it in the SQL dataflow option to be the most effective. When I use ETL to aggregate it seems like you can only get it to work on one field to aggregate, but SQL is pretty easy for multiple aggregations.

  • swagner
    swagner Contributor

    @user02674 (Adam)

     

    Good news!  I understand the application you are describing below.  (I work in distribution)

     

    I refer to this as dependent demand.  Your store usage (demand) is essentially rolled up in forecasting inventory levels at the DC served (DC also may have it's own independent demand to consider).

     

    If this is still an open issue for you, let me know if you want to jump on a call and discuss.  I'm not an IT guy, but maybe between us we can run through the logic and figure this thing out.

     

    Let me know,

     

    Scott 

  • Hey Scott,

     

    I don't have a ton of experience using SQL to do aggregations. My main question is when I do it should I be setting a where statement to limit the number of days being aggregated? I tried to run SQL statement without it, but it ran for 5 hours before I killed it. Any thoughts or examples?

     

    Thanks,

    Adam