Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This may be
Sum({$<MonthYearField= {“>=$(=Max('$(VLastMonth)') – 11)<=$(=Max('$(VLastMonth)'))”},
Year = ,
Quarter = ,
Month = >} YourMeasure)
Hope it helps.
Thank you, but it did not work. I think my problem is Qlik Sense is not recognizing my VlastMonth as type date.
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