Last 12 Months

[#1]

Hi All,

Need to show last 12 months of data based on existing date filed. How can i achive this ?

Thanks

[jagan · Accepted solution]

Hi,

Try this expression

='QTY= ' & sum({<Trans_Date={'>=$(=MonthStart(Max(Trans_Date), -11))<=$(=Max(Trans_Date))'}>}QTY)

Create variable with vMaxDate = Max(Trans_Date) and then use below expression

='QTY= ' & sum({<Trans_Date={'>=$(=MonthStart(vMaxDate, -11))<=$(=vMaxDate)'}>}QTY)

Hope this helps you.

Regards,

Jagan.

[#3]

Hi Jagan,

Is it possible to load last 12 months of data while loading the data in the script ?

[jagan]

Hi,

Use script below to reduce last 12 months in script level

DataTemp:

Data:

LOAD Max(Trans_Date) AS MaxDate

FROM

Last12Months.xlsx

(ooxml, embedded labels, table is Sheet1);

let vMaxDate = Peek('MaxDate');

Data:

LOAD Trans_Date,

     QTY

FROM

Last12Months.xlsx

(ooxml, embedded labels, table is Sheet1)

where Trans_Date >= AddMonths($(vMaxDate), -12) AND Trans_Date <= $(vMaxDate);

Regards,

Jagan.