Rolling period table

[shamitshah]

Hi,

I require help with rolling data.

Table 1 is the raw data. I created table 2 to show the rolling amounts with the help of information on the forum. This table is working.

Any ideas on how I could create the third table which would show the latest rolling data by Product (basically 2 columns - Product and the latest Rolling Quantity).

Thanks

Shamit

[sunny_talwar]

Some of the months are missing, do you want last 12 months from max date or any available last 12 months.

For last 12 months you can try this:

Sum({<ShipDate = {"$(='>=' & Date(MonthStart(Max(ShipDate), -11)) & '<=' & Date(MonthEnd(Max(ShipDate), 0)))"}>}Quantity)

[shamitshah]

Hi Sunny,

Thanks for the above. That work's well for the last available 12 months as you mention above.

Any idea on the expression if we wanted the last 12 months from max date i.e from today.

Thanks

Shamit

[sunny_talwar · Accepted solution]

For 12 months from today, you can simply use this:

Sum({<ShipDate = {"$(='>=' & Date(MonthStart(Today(), -11)) & '<=' & Date(MonthEnd(Today(), 0)))"}>}Quantity)

[shamitshah]

Thanks Sunny!