topic Re: Aggr and Median Function in QlikView

Aggr and Median Function

Anonymous — Fri, 17 Nov 2017 04:33:27 GMT

Dear all,

I have no idea how the aggr and median function works in QlikView. I have the data below.

FACT_PA_NO	Fiscal Year	Division	TotalCash	FiscalNumYear	FiscalMonth
7141	FY13/14	Motors	709.5	2013	1
7141	FY13/14	Motors	720	2013	2
7141	FY13/14	Motors	1032	2013	5
7141	FY13/14	Motors	1039	2013	3
7141	FY13/14	Motors	1045	2013	8
7141	FY13/14	Motors	1046.5	2013	7
7141	FY13/14	Motors	1047	2013	4
7141	FY13/14	Motors	1067.5	2013	6
7141	FY13/14	Motors	1074.5	2013	9
7141	FY13/14	Plantation	1111.5	2013	10

When I create a chart using the median and aggr function, Qlikview will show me the not consistent result which means the somehow expression result will assign to different Division after reload every time.

Can anyone tell me how the QlikView actually work with the aggr function? Besides, if I wanted to force the expression result according to the Max FiscalMonth, how can I do it? The attachment is my test application.

Re: Aggr and Median Function

jonathandienst — Fri, 17 Nov 2017 04:47:50 GMT

You need to include the division in the aggr() dimension list.

Re: Aggr and Median Function

tresB — Fri, 17 Nov 2017 04:51:32 GMT

What is your expected output, this?

If so, you have just put NODISTINCT in aggr(), like:

median( aggr(NODISTINCT sum(TotalCash),FACT_PA_NO,FiscalNumYear))

If you want to understand more on aggr() working, refer: AGGR...

Re: Aggr and Median Function

Anonymous — Fri, 17 Nov 2017 05:32:07 GMT

Thank for reply. I expected the result will assign to Plantation Division because FiscalMonth for Plantation is the max.

Re: Aggr and Median Function

Anonymous — Fri, 17 Nov 2017 05:34:03 GMT

Thks for the reply. Since I have different dimension selected in my application, that why I can not hard code the division in the aggr().