Define date range through variables

[MaryanVegaC]

Hi,

I'm trying to create a variable to define a time range (12 months). One of my fields is reporting month in the format 'YYYY-MM', and from one variable that I already set (input box variable) I got the reference month where I will determine my second variable and define my 12 months time range. Here it is what I'm trying to do:

">=$(=Date(AddMonths('$(VLastMonth)',-11)))<=$(=Date(MonthEnd('$(VLastMonth)')))"

VLastMonth is a input box variable.  For example is VLastMonth= 2021-12, I will get the next result from my previous formulation ">=2021-01<=2021-12", but when I tried to use this information to calculate the total sales within this time range it doesn`t work. I`m trying the next formulation:

sum({$<reportingMonth={'($(vLast12MonthsPeriods))'}>}sales)

from this I'm getting 0. 

Anyone can help me?, thanks in advance.

[BrunPierre]

This may be

Sum({$<MonthYearField= {“>=$(=Max('$(VLastMonth)') – 11)<=$(=Max('$(VLastMonth)'))”},
Year = ,
Quarter = ,
Month = >} YourMeasure)

Hope it helps.

[MaryanVegaC]

Thank you, but it did not work. I think my problem is Qlik Sense is not recognizing my VlastMonth as type date.

[MaryanVegaC]

Hello,

I found the problem, Qlick Sense does not recognize my month field as a date. As soon I created a second column in the load editor, converting my original month field column as date type the previous formulation worked.

This is the code I used to adjust my original month field in the data load editor: 

date#(reportingMonth) as reportingMonth2