Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
If I'm not mistaken I believe you need to add a dollar sign expansion:
Sum({<[Reporting YYYYMM] = {"$(=Date(Date#('$(vEndOfReportingMonth)', 'MMM YYYY'),'YYYYMM'))"}>} Value)
Are the first two set expression working perfectly, individually?
If I'm not mistaken I believe you need to add a dollar sign expansion:
Sum({<[Reporting YYYYMM] = {"$(=Date(Date#('$(vEndOfReportingMonth)', 'MMM YYYY'),'YYYYMM'))"}>} Value)
Ohh, I was so close - this fixed it!
Thank you, henrikalmen.