Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Aggr and Median Function

Dear all,

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

FACT_PA_NOFiscal YearDivisionTotalCashFiscalNumYearFiscalMonth
7141FY13/14Motors709.520131
7141FY13/14Motors72020132
7141FY13/14Motors103220135
7141FY13/14Motors103920133
7141FY13/14Motors104520138
7141FY13/14Motors1046.520137
7141FY13/14Motors104720134
7141FY13/14Motors1067.520136
7141FY13/14Motors1074.520139
7141FY13/14Plantation1111.5201310

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.

Capture.JPG  Capture1.JPG

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.

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
tresesco
MVP
MVP

What is your expected output, this?

Capture.JPG

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...

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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().