Nested Aggregation

[#1]

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.

[sunny_talwar]

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')

[#3]

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.

[sunny_talwar]

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

[#5]

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.

[sunny_talwar]

Would you be able to share a sample?

[#7]

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.

[sunny_talwar]

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')

[#9]

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.

[sunny_talwar · Accepted solution]

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')