Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
You need to include the division in the aggr() dimension list.
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...
Thank for reply. I expected the result will assign to Plantation Division because FiscalMonth for Plantation is the max.
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().