Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have some cost data in which I have the cost, the date the cost was posted (Posting Month) and the date the cost actually happened (Actual Month).
I would like to display in a table (Dimension is Posting Month) the % of cost that happened and is being posted in the same month (i.e. Posting Month = Actual Month).
Normally, I do this with Set Analysis but I am not sure how I can incorporate the above condition (Posting Month = Actual Month). For a single month (in this case June 2017) my formula would be:
Sum({$< [Actual Month]={'2017 06'} >} [Cost])/Sum({$< [Posting Month] = {'201706'} >} TOTAL <[BA]> [Cost])
Posting Month | Amount (€) | Amount (%) |
201701 | 100,000 | 0% |
201702 | 100,000 | 0% |
201703 | 100,000 | 0% |
201704 | 100,000 | 0% |
201705 | 100,000 | 0% |
201706 | 100,000 | 46% |
201707 | 100,000 | 0% |
201708 | 100,000 | 0% |
Use =if([Actual Month]=[Posting Month],Sum([Amount (€)]))/sum([Amount (€)])
Hi,
I would recommend to create a flag in script to know if both the dates are in same month.
Load *,If(Monthname(PostingDate) = Monthname(ActualMonth), 1,0) as MonthFlag
From xyz;
Now in set analysis use below expression.
Sum({$<MonthFlag = {"1"}>} [Cost])/Sum([Cost])
Regards,
Kaushik Solanki
Or
If([Actual Month]=[Posting Month],Sum( [Cost]))
/Sum(TOTAL <[BA]> [Cost])
in your case
Regards
Pratyush