Sum based on condition with dimension

[Anonymous]

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%

[prat1507]

Use =if([Actual Month]=[Posting Month],Sum([Amount (€)]))/sum([Amount (€)])

[kaushiknsolanki]

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

[prat1507]

Or

If([Actual Month]=[Posting Month],Sum( [Cost]))

/Sum(TOTAL <[BA]> [Cost])

in your case

Regards

Pratyush