Variable within Expression within Set Analysis

[gaber]

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] = {"=...

[Gabbar]

Are the first two set expression working perfectly, individually?

[henrikalmen · Accepted solution]

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)

[gaber]

Ohh, I was so close - this fixed it!

Thank you, henrikalmen.