topic Re: How to write the expression in KPI in QlikView

How to write the expression in KPI

kunkumnaveen — Fri, 31 Jan 2020 09:26:03 GMT

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

Re: How to write the expression in KPI

kunkumnaveen — Fri, 31 Jan 2020 09:58:23 GMT

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

Re: How to write the expression in KPI

Brett_Bleess — Mon, 24 Feb 2020 21:31:32 GMT

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