Using Qlik Sense, I have a relatively complex SQL query which is to be visualised within the P&L chart. The data is aggregated monthly, but I need only the chose month to be filtered out at a time.
As this chosen month needs to be used across the App, I need it to be a variable - vEndOfReportingMonth which is a date formatted 'MMM YYYY' for readability purposes.
Using a Variable Input chart for this variable, I set the dynamic values using the function below, limiting the choices from the previous month the start of the reporting dataset.
=Concat({<MonthYearShort -= {"$(=Text(Date(Today(), 'MMM YYYY')))"}>} Distinct Date(MonthEnd(PaidDate), 'MMM YYYY'), '|', -[PaidDate.autoCalendar.YearMonth])
I understand the Variable Input tool returns the result as a string, not as a date. I plan on using it to filter data date formatted as YYYYMM, so for it to be used as a date, I need to do something along the lines of the following:
=Date(MonthEnd(Date#('$(vEndOfReportingMonth)', 'MMM YYYY')),'YYYYMM')
Wonderful, now I want to use this variable in a set expression to filter data using the [Reporting YYYYMM] field. However, I simply cannot get the script to work, basing it off a structure like below. It seems to ignore the filtering and return all values.
Sum({<[Reporting YYYYMM] = {"=(Date(Date#('$(vEndOfReportingMonth)', 'MMM YYYY'),'YYYYMM'))"}>} Value)
If I simplify the code to test data types, the code below effectively filters the data, so it appears Text works.
Sum({<[Reporting YYYYMM] = {'202212'}>} Value)
But when I try converting the variable to Text() like so, zeroes are returned.
Sum({<[Reporting YYYYMM] = {"=(Text(Date(Date#('$(vEndOfReportingMonth)', 'MMM YYYY'),'YYYYMM')))"}>} Value)
Could someone please provide some guidance.
- Are the data types not handled as I'm expecting?
- I've tried changing the double quotation marks to single apostrophes marks and doubling the apostrophes within the set analysis expression, but that returned nothing.
- Is there any way to enter an expression on both sides of a set expression? i.e. {<Text([Reporting YYYYMM] = {"=...
