Last 12 Months (End Month)

[pgalvezt]

Hello,

Currently, I am showing in a line graph, the sum of the ticket field every month end, that is to say that each point of the graph corresponds to the end of the month (Jan 31, Feb 28, So on) ... For this in the script I have created a flag that positions me every month end and bring the data. If (Fecha= Floor (MonthEnd (Fecha)), 1, 0) as MonthEndDateFlag,

And in the graph as dimension I have the month...

and as a measure:

Num (sum ({<MonthEndDateFlag = {'1'}, Month, Day =>} if (Fecha> = Date_Allocation and Fecha<= date_Reserve, Tickets)), '#. ## 0.00') ....

This works fine, but since I am filtering 2020, it only shows me from January 2020 onwards, I would like to show this but 12 months ago, If I filter July, I would like to show until June 2019, independent of the selection of filters Year, Month and Day.

Thanks!

[jwjackso]

Since you are already creating a MonthEndDateFlag, why not add a MonthDiff field which is the number of  months prior to today .

((year(today(2))*12)+month(today(2))) - (((year([Fecha])*12)+month([Fecha]))) as MonthDiff

Add the MonthDiff to the SetAnalysis and don't filter on 2020

Num (sum ({<MonthDiff={">12"},MonthEndDateFlag = {'1'}, Month, Day =>} if (Fecha> = Date_Allocation and Fecha<= date_Reserve, Tickets)), '#. ## 0.00') ...

[pgalvezt]

Hello,

Thank you for your reply,

By implementing your suggestion, and deselecting the year 2020 (which the user will not want, because he is seeing other objects that need the year 2020 to be selected) ... but in the same way the graph is only accumulating the sum for year and not showing the last 12 months from this month. He is calculating me every month and year.

[jwjackso]

You can add Year= into the Set Analysis expression so that it ignores the Year filter.

You can also explore Henric's AsOfDate table.  I've used it many times.

[pgalvezt]

Hello,

I have followed the example of Henric_Cronström, and I have managed to show in the line chart all the results of the month-end days. (At each point is the end of the month). What I have not been able to achieve, is that when positioning myself in the current month, it shows me the same result but 1 year ago, that is, if I have July 2020 selected, the graph will show me until June 2019. If I select June 2020, The graph shows me until May 2019 and so on (Make it mobile 12 months ago). How can be the final formula?

Actually my formula is:

Num (sum ({$<MesDif={"0"} ,MonthEndDateFlag = {'1'}, Year=, Month=, Day=>} if (Fecha >= Date_Allocation and Fecha <= date_Reserve, Tickets)),'#.##0,00')

Dimension AsOfMonth.

[jwjackso]

Can you show the script that generates the AsOfDate table?