Nested aggrgation

[dominic_marchan]

Hi

I am trying to calculate a KPI that will show me on a specific day the number of items below a certain level of inventory.

My data data is broken down by date, model, warehouse, inventory

date	model	warehouse	inventory
1/2/2020	A	11	64
1/2/2020	A	4	18
1/2/2020	B	11	53
1/2/2020	B	4	27
1/2/2020	C	11	18
1/2/2020	C	4	84

My Attempt:

Count(Aggr(Count({$<date = {"1/2/2020"}, inventory = {"<=20"}>}model),model))

The result of this formula is 2, but the expected result would be 0. I want the formula to look at the model level and not the warehouse level (inventory should be summed at the model level so giving the results based on the below table)

Model	Inventory
A	82
B	80
C	102

Hence the number of models below 20 units should be 0

Thanks for your help

[robert99]

@dominic_marchan 

I'm sure there's lots of different ways to do this

I try to avoid aggr if possible. Like so

SCRIPT

LOADDATA:
Load * Inline [
date,model,warehouse,inventory,
1/2/2020, A, 11, 64,
1/2/2020, A, 4, 18,
1/2/2020, B, 11, 53,
1/2/2020, B, 4, 27,
1/2/2020, C, 11, 18,
1/2/2020, C, 4, 84,
2/2/2020, A, 11, 64,
2/2/2020, A, 4, 18,
2/2/2020, B, 11, 53,
2/2/2020, B, 4, 27,
2/2/2020, C, 11, 1,
2/2/2020, C, 4, 3,
];

Sum:
Load
date ,
model,
SUM(inventory) as SumInvScript
resident LOADDATA
GROUP BY date , model ;

or you could join date & model to avoid a synthetic join

Measure

count({<SumInvScript = {"<=20"}>} model) 

gives 1 in total. Or 0 and 1 if filtered by the various dates

Or try this Measure

sum( aggr(if(sum(inventory)<=20,1,0),date,model))

[tm_burgers · Accepted solution]

I would use:

sum(if(Aggr(sum({<date={"1/2/2020"}>}inventory),model, date)<20,1,0))

and make the date a variable to be the date you want it, or a "today()"

[NitinK7]

Hi,

please refer below code that will be helpful to you for getting result

need some calculation in backend

PQR:
LOAD * INLINE [
date, model, warehouse, inventory
1/2/2020, A, 11, 64
1/2/2020, A, 4, 18
1/2/2020, B, 11, 53
1/2/2020, B, 4, 27
1/2/2020, C, 11, 18
1/2/2020, C, 4, 84
];

Left Join(PQR)
ABC:
LOAD
model,
Sum(inventory) as tot_inventory

Resident PQR
Group by model;

and take textbox and write expression like

Count({<tot_inventory={'>=20'}>}model).

Regards,

Nitin