Sum of previous month with Date in Dimension

[crusader_]

Hi all,

I have an issue with calculating Sum of previous months.

tmp:

LOAD * INLINE [

  Consumed, Rest, Date

  100, 1000,'03.06.2014'

  200, 900,'26.06.2014'

  140, 700,'23.09.2014'

  300, 560,'07.05.2014'

  175, 260,'29.04.2014'

];

Date	Consumed
29.04.2014	175
07.05.2014	300
03.06.2014	100
26.06.2014	200
23.09.2014	140

I need to make a Straight table with 2 columns

1. Date

2. sum(Consumed) which belongs to previous 3 months.

Desired result is

Date	Sum
29.04.2014	0
07.05.2014	175
03.06.2014	475
26.06.2014	475
23.09.2014	300

I've made an expression in a such way

sum({<Date={">=$(=MonthStart(Date,-3))<=$(=MonthEnd(Date,-1))"}>} TOTAL Consumed)

and it shows me expected result but if only I choose exact date.

Date	SUMM
26.06.2014	475

How can I achieve desired result using Set Analysis only?

Thanks in avance.

Andrei

[crusader_]

It should be very graceful solution but I can't find it.

If it possible at all.

Any help will be appreciated!

Thanks.

[tresesco · Accepted solution]

Set analysis doesn't get evaluated Row-wise; it filters data once for an object. Hence, directly using set analysis you would not get what you want, rather try with IF. This is one case when IF is the answer rather than set analysis. Otherwise, if you raelly want to stick to set analysis, another alternative and not-so-elegant way could be using synthetic dimension. Recommended better way - handle in the script.

[#4]

can you try to us intervalmatch ? by creating another table

[mohsinqlik]

Hi am facing the same scenario where i am not getting value of 6 months back when month as dimension is present.

Kindly share if you found a way to get the solution.