topic Re: Nested Aggregation in QlikView

Nested Aggregation

Wed, 30 Nov 2016 14:35:21 GMT

Hello,

I am trying to achieve the below to fit into a single textbox, please give me your suggestions.

Fiscal Period	Country	=Num(FirstSortedValue( Aggr(Sum(PRC_USD_AMT),SLS_RPT_TRNS.INVC__DT,CTRY_NAM),-Aggr(DT_CONS_DIM.FISC_DAY_OF_MTH_NBR,SLS_RPT_TRNS.INVC__DT,CTRY_NAM))/1000000,'#,###,##0.00 M')

201610	Mexico	3.45 M
201610	Germany	11.96 M
201610	India	13.27 M
201610	US	33.92 M
	Sum	64.86 M

I want the Sum to be displayed in a text box. It is the Sum of Sales for the most recent date for each country.

please let me know if more information is needed.

thanks.

Re: Nested Aggregation

sunny_talwar — Wed, 30 Nov 2016 14:37:00 GMT

Try this:

=Num(Sum(Aggr(FirstSortedValue(Aggr(Sum(PRC_USD_AMT),SLS_RPT_TRNS.INVC__DT,CTRY_NAM),-Aggr(DT_CONS_DIM.FISC_DAY_OF_MTH_NBR,SLS_RPT_TRNS.INVC__DT,CTRY_NAM))/1000000,

[Fiscal Period], Country)),'#,###,##0.00 M')

Re: Nested Aggregation

Wed, 30 Nov 2016 14:51:25 GMT

Thanks for your reply Sunny.

The above table is just for explaining the requirement, so the expression cannot be used with Fiscal Period. So i tried to replace it with the INVC__DT and it does not give me the right value.

Every country will have multiple entries for dates, and i will have to pick only the most recent value for every country and show the Sum in a text box.

Thanks.

Re: Nested Aggregation

sunny_talwar — Wed, 30 Nov 2016 14:54:21 GMT

Why can we not use Fiscal Period? I am not sure I understand that part

Re: Nested Aggregation

Wed, 30 Nov 2016 14:57:41 GMT

I tried with Fiscal Period as well, it does not give me the required value of 64.86.. I will try to upload a sample document.

Re: Nested Aggregation

sunny_talwar — Wed, 30 Nov 2016 15:00:48 GMT

Would you be able to share a sample?

Re: Nested Aggregation

Wed, 07 Dec 2016 13:53:36 GMT

Hello Sunny,

Attached a sample here. I would like to get the Sum of Sales for all the countries for their most recent dates and display in a text box.

Thanks.

Re: Nested Aggregation

sunny_talwar — Wed, 07 Dec 2016 14:03:08 GMT

Can you try this:

=Num(Sum(Aggr(FirstSortedValue(Aggr(Sum(PRC_USD_AMT),INVC__DT,CTRY_NAM),-Aggr(FISC_DAY_OF_MTH_NBR, INVC__DT,CTRY_NAM)), DT_CONS_DIM.FISC_PER_CD, CTRY_NAM, INVC__DT))/1000000,'#,###,##0.00 M')

Re: Nested Aggregation

Wed, 07 Dec 2016 14:33:05 GMT

Expected value is 65.66 M which i get if i make a selection on the INVC__DT column, but i need this value without any selection.

Most recent values for India is on 18th, Germany on 17th, US on 17th and Mexico on 17th.. Which totals to 65.66 M.

Re: Nested Aggregation

sunny_talwar — Wed, 07 Dec 2016 14:43:08 GMT

Try this:

=Num(Sum({<DT_CONS_DIM.FISC_PER_CD = {"$(=Max({<PRC_USD_AMT = {'*'}>}DT_CONS_DIM.FISC_PER_CD))"}>}Aggr(FirstSortedValue(Aggr(Sum(PRC_USD_AMT),INVC__DT,CTRY_NAM),-Aggr(FISC_DAY_OF_MTH_NBR, INVC__DT,CTRY_NAM)), DT_CONS_DIM.FISC_PER_CD, CTRY_NAM))/1000000,'#,###,##0.00 M')

Re: Nested Aggregation

Fri, 09 Dec 2016 09:49:05 GMT

This seems to work for me. Thanks Sunny.