How to create a Rolling-12 month chart that displays Month-Year, and Amount that is also not affected by filtration

[asheppardwork]

I have a dataset that is [Month], [Year], and [Amount].  I am trying to show a table of the Month-Year next to the Amount for the last twelve months as the data is refreshed weekly.  However, the extra ask is that this table should not be affected by any other filters.

So far using a VizLib Container and a VizLib Straight Table, I have made a alternate state where nothing is filtered and then applied that state to both the container and table to keep from having the filters applied.  Yet, I cannot seem to get the table to just show the last twelve months.

I have got through many iterations.  This is what I have currently.  Any help would be much appreciated.

Dimension Script for the Month-Year Column:

=DATE(MONTH&'/1/'&YEAR,'MMM-YY')

Measure Script for the Amount Column:

=IF(Date(MONTH&'/1/'&YEAR,'YYYY-dd-MM')>=Date(Max(AddMonths(Date(MONTH&'/1/'&YEAR,'YYYY-dd-MM'), -11))),Sum({<Date={">=$(=Date(AddMonths(Max({<DATE([MONTH]&'/1/'&[YEAR],'YYYY-MM-DD')>})), -11, 'YYYY-MM-DD'))<=$(=Max({<DATE([MONTH]&'/1/'&[YEAR],'YYYY-MM-DD')>}) Date))"}>} [AMOUNT]),0)

Currently I get the entire dataset of twenty-four months, when I should only get twelve; as the last month was April I would expect April-22 through April-23

Example of what I'm getting:

Jan-22 12345.98
Feb-22 7890.38
Mar-22 121314.64
Apr-22 151617.24
May-22 171819.84
Jun-22 202122.68
Jul-22 86753.46
Aug-22 912.04
Sep-22 10131.46
Oct-22 1717.78
Nov-22 131313.88
Dec-22 2435.8
Jan-23 89568.5
Feb-23 12345.48
Mar-23 11122.44
Apr-23 77789.08

Example of what I want:

Apr-22 151617.24
May-22 171819.84
Jun-22 202122.68
Jul-22 86753.46
Aug-22 912.04
Sep-22 10131.46
Oct-22 1717.78
Nov-22 131313.88
Dec-22 2435.8
Jan-23 89568.5
Feb-23 12345.48
Mar-23 11122.44
Apr-23 77789.08

Update:

here is a new clue, if I hard code the date I want to be the max date it works, but the formula doesn't, why?

=IF(Date(MONTH&'/1/'&YEAR,'YYYY-dd-MM')>=Date('4'&'/1/'&'2022','YYYY-dd-MM'),Sum({<Date={">=$(=Date(AddMonths(Max({<DATE([MONTH]&'/1/'&[YEAR],'YYYY-MM-DD')>})), -11, 'YYYY-MM-DD'))<=$(=Max({<DATE([MONTH]&'/1/'&[YEAR],'YYYY-MM-DD')>}) Date))"}>} [AMOUNT]),0)

[asheppardwork · Accepted solution]

I found a answer from Sunny_talwar and applied it to my situation - https://community.qlik.com/t5/QlikView-App-Dev/Show-all-rows-with-highest-value-of-FieldB-w-r-t-each...

What I needed to do was find the 'max' date from all the rows of dates then use that as the comparison.  here is the sample- 

=If([END_DT.autoCalendar.MonthYear] >= Date(AddMonths(Max(TOTAL <END_DT.autoCalendar.MonthYear> END_DT.autoCalendar.MonthYear),-11)), NUM(SUM([AMOUNT]),'$#,##0.0', '.', ','))

I did have to make one adjustment to my data to make it easier to code instead of using the [MONTH] and [YEAR] fields from the data set I had to add a 'autoCalendar' table in my loads to make it easier to mash up the dates  here is an example of that -

[autoCalendar]:
 DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
 Dual(Month($1)&'-'&Year($1), monthstart($1)) AS [MonthYear] Tagged ('$axis', '$monthyear', '$qualified'),
DERIVE FIELDS FROM FIELDS [END_DT] USING [autoCalendar];

After that I suppressed zero values and the table now only shows the last rolling 12 months from the last date backwards, plus the VizLibCotainer with the AlternateStates keeps it from being filtered.

So after four days I have something that takes 10 minutes in any other BI tool ever (ever).