topic Re: Get last 12 months rolling data based on Month selection in App Development

Get last 12 months rolling data based on Month selection

Ipsita20191 — Fri, 15 Nov 2024 21:43:53 GMT

Hi Folks,

I need one help . I have one Year and a Month Filter as a drop down.

If I select 2023 as a Year and Feb as a month from drop down selection,

Sum (Sales) : should return data from 2023 Feb to 2022 Feb (Last 12 months).

Note : Month drop down has been declared as a varibale : $(vMonth)

Can you please help on that ?

Thanks

Re: Get last 12 months rolling data based on Month selection

Sabrina_V — Mon, 05 Jun 2023 07:18:31 GMT

You can do it in the set analysis using the function AddMonth (Month, -12)

Re: Get last 12 months rolling data based on Month selection

srchagas — Mon, 05 Jun 2023 08:02:47 GMT

Hi

I recommend you to have DATE in numeric format, because it make the thinks easy to make the set analysis.

Left assume you have 01/02/2023 selected, so its this as number you create a variable like this:
Ps; I recommend 2 variables:

vL.MaxDate = Num(Floor(Max(YOUR_DATE_FIELD)))
vL.LastYearDate = Num(Floor(Max(YOUR_DATE_FIELD))) - 365

Once you have this 2 variables you set analysis will be

//New Set Analysis Way {< YOUR_DATE_FIELD =, YOUR_DATE_FIELD = ">=$(vL.LastYearDate) <=$(vL.MaxDate) >} Sum(Sales) //Old Set analysis Way Sum({< YOUR_DATE_FIELD =, YOUR_DATE_FIELD = ">=$(vL.LastYearDate) <=$(vL.MaxDate) >} Sales)

Don't forget to add all your possible date fields to be ignored using: FIELD =, in the set analysis, because then you saying:

Ignore what the user select and calculate use what i sent to you.

this works 100%