topic Re: Function aggr doesn't return values based on selections in QlikView

Function aggr doesn't return values based on selections

Fri, 15 Feb 2013 14:15:28 GMT

Hello All,

I want to use the aggr function to return me the values of Inventory Coverage(IC) from week 1 to Week 4 using filters like, current year, current semester(1 if we are in Week 1 to Week 26 and 2 if we are in Week 27 to Week 52), and where the countries are France, UK, Poland and Spain.

I have made 2 tests: one which returns me the values of IC for each week based on the filters above and the second test which makes an aggregation and should return me the values of IC from Week1 to Week 4 for countries France, UK, Poland and Spain based on current year and current semester.

Column 3 in the attached table returns me the correct values of IC based on the filters and expression below:

=(SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}EOW_STK_QTY)/SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}Avg_Shpmt))*5

But when I use the aggr function below(4th column in the attached table), qlikview doesn't return me the values of the IC for each country (France, UK, Spain, Poland) based on current year, current semester:

SUM(aggr((((SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}EOW_STK_QTY))/(SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}Avg_Shpmt)))*5),%WEEK))

v_Report_year = 2013

v_Current_Half= 1 as the data in the qvd are for week 4

v_Filtre_Europe = Cluster Europe, that is, France, O UK, TM UK, Poland, Spain

It's only returning me values for France and Spain and only for some weeks instead of for weeks 1 to 4.

Can you please help me on this issue?

Thanks

Re: Function aggr doesn't return values based on selections

Anonymous — Fri, 15 Feb 2013 14:24:11 GMT

Try using 'nodistinct' as a keyword just inside the aggr() function .....

SUM(aggr(NODISTINCT (((SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}EOW_STK_QTY))/
(SUM({$<%YEAR={$(v_Report_Year)}, $(v_Filtre_Current_Half), $(v_Filtre_Europe)>}Avg_Shpmt)))*5),%WEEK))

Not sure why, but I often need to set this. Reference manual states:

"If the expression argument is preceded by the nodistinct qualifier, each combination of dimension values may generate more than one return value, depending on underlying data structure"

Jonathan

Re: Function aggr doesn't return values based on selections

Mon, 18 Feb 2013 15:02:21 GMT

Thanks a lot.

At least I get values of IC for all the countries. I can now cross check if the expression returns me the values I was expecting.