Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using following AGGR Function
MAX(Aggr(Sum(SALES_IM_QTY),DWH_SKU_REF_NO,DWH_IM_MONTH))
to find the amount of Sales in selected month and it gives me the result alright.
But if I want an expression which can also tell me the month it has taken in all the selected months i am not able to get it.
I tried
FirstSortedValue(DWH_IM_MONTH,-Aggr(Sum(SALES_IM_QTY),DWH_IM_MONTH, DWH_SKU_REF_NO))
but still not.
Please if any of you experts can Help me I will be very thankful to you.
You can use if statement to compare the month with sum of every month through set analysis.
Hi Muhammad,
Sorry i did not get your question can u please elaborate...or post your app
Thanks for your interest mate,
Well What I am looking is to have Maximum selling month of the selected period.
DWH_IM_MONTH | SALES_IM_QTY
May | 2300
June | 5000
July | 2000
Where June is a Max selling month.
CAN YOU WRITE THE FORMULA PLEASE
FirstSortedValue(DWH_IM_MONTH,-Sum(SALES_IM_QTY))
Hi,
how about this:
Max(Aggr(Sum(SALES_IM_QTY), DWH_IM_MONTH. DWH_IM_YEAR))
I suppose there's a field called DWH_IM_YEAR in your data model or maybe if you have a field with the concatenation of Month and Year, then it would be better. The thing is that you need to aggregate your sales per month and per year (maybe also by DWH_SKU_REF_NO) and then you can get the max value,
regards
THANKS BUT ACTUALLY THIS IS WHAT I WAS THINKING THE TABLE WILL BE CREATED IN AGGR FUNCTION.