Multiple Level Aggregation

user02674

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

KVincent

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

@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 

user02674

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