Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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')
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')
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.
Why can we not use Fiscal Period? I am not sure I understand that part
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.
Would you be able to share a sample?
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.
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')
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.
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')