Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
With this Sum Aggr function below, when I use a Month Year filter and select months other than Jan-2023 and Feb-2023, the amount returned do not include summation of the 2nd condition in the formula:
(if(wildmatch({<[Month Year]-={"Jan-2023","Feb-2023"}>} [Customer_Code],'A'),
43000,
meaning, this condition is skipped and not added to the total amount. May I have guidance on what need to be corrected in order for this 2nd condition to work and included in the aggr sum amount?
(sum(Aggr(
(if(wildmatch({<[Month Year]={"Jan-2023","Feb-2023"}>} [Customer_Code],'A'), 0,
(if(wildmatch({<[Month Year]-={"Jan-2023","Feb-2023"}>} [Customer_Code],'A'), 43000,
(if(wildmatch([Customer_Code],'B'), 43000,
(if(wildmatch([Customer_Code],'C'), 20000,
(if(wildmatch([Customer_Code],'D'), 55000,
(Sum({<[Month Year]={'$(=Max(Date([Month Year], 'MMM-YYYY')))'},
[Customer_Code]=-{'A','B','C','D'}>} USD_Revenue))
))))))))))
,[Customer_Code])))
Thanks
@newbie_sm I see you are combining the Set Analysis expression in the if Condition which is not right, If you could elaborate on your issue and provide us the sample data and output. It will help us to provide you the right solution.
The scenario is that:
For months <=DEC-2022, I have customer revenue:
A&B=43000, C=20000, D=0 - these values are not loaded into the dataset and customers= E,F,G,H with revenue that are pre-loaded in the dataset.
For months >DEC-2022, I have customer revenue:
A=0, B=43000, C=20000, D=55000 - these values are not loaded into the dataset and customers= E,F,G,H with revenue that are pre-loaded in the dataset.
So, I need a formula that allows calculate for these scenarios that will response to the Month-Year dimension calculation and read the max month which is Feb-2023