How to Get Last 3 Month Records

[vaibhav_rathore]

Hi,

I have a requirement to get the last 3 months record (sum) as below:

have fields as:

Period----------- Value -----------Last 3 months

11.2016 --------100-------- Total of Nov, Oct and Sept

12.2016 --------50--------- Total of Dec, Nov and Oct

01.2017 --------100--------- Total of Jan, Dec and Nov

02.2017-------- 200--------- Total of Feb, Jan and Dec

03.2017--------- 10--------- and so on.........

04.2017--------- 30----------

The Period field is of Data Type: Text

Suggestions please.

Regards

[amitrathi239]

follow below steps.May be this is not a good solution but i believe you can achieve this.You have to write long if-else condition for all 12 months.

1) Create V Month variable=MonthNumberOfYear(ToDate([Period];"MM.yyyy"))

above will display the month number.

2) Create V Year variable=Year(ToDate([Period];"MM.yyyy"))

3)Sort V Year on Ascending.

4) Sort V Month on Ascending.

5) Create V Value variable as dimension.

5) Final Variable:

=If([V Month]=12) Then RunningSum(([V Value]) Where ([V Month]=12)+ ([V Value]) Where ([V Month]=11)+ ([V Value]) Where ([V Month]=10)) ElseIf([V Month]=11) Then RunningSum(([V Value]) Where ([V Month]=11)+ ([V Value]) Where ([V Month]=10)+ ([V Value]) Where ([V Month]=9)) ElseIf([V Month]=1) Then RunningSum(([V Value]) Where ([V Month]=1)+ ([V Value]) Where ([V Month]=12)+ ([V Value]) Where ([V Month]=11))

Similar to above you need to add other months also in elseif condition.

If everything works then hide the V Month and V Year columns in table.

[vaibhav_rathore]

Hi Amit,

Thank you for the reply. I tried this but was getting #multivalue.

I followed an alternative approach, created 3 variables:

v1: Value

v2: previous(value)

v3: previous(previous(value))

sum: v1+v2+v3

this gave me the desired answer. 🙂

Regards