Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I need to count number of NOD whose NOD are <20 & >30 in two different KPI's
From the below image
KPI 1:<20 NOD : 5,
KPI 2: >30 NOD : 2
Dimension : Store, store Detail, Brand
NOD Expression :
(
aggr(sum(mat_count),Store,store Detail,Brand)
/
alt(
avg({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}
aggr(
Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}bil_count)
,Store,store Detail,Brand)
),0)
) *30
Thanks
I tried some thing like below ,but it is showing 0 Value
sum(aggr(
if(
(
(
aggr(sum(mat_count),Store,[stock Details],Brand)
/
alt(
avg({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}
aggr(
Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}billing_count)
,Store,[stock Details],Brand)
),0)
) *30
)<20,1,0),Store,[stock Details],Brand))
What am i missing in above expression
Have a look at the following Design Blog post on AGGR and Set Analysis, hopefully that may have what you need to get things working properly.
https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822
Regards,
Brett