Variable within Expression within Set Analysis

gaber — Thu, 02 Mar 2023 03:22:13 GMT

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

Re: Variable within Expression within Set Analysis

Gabbar — Thu, 02 Mar 2023 06:43:01 GMT

Are the first two set expression working perfectly, individually?

Re: Variable within Expression within Set Analysis

henrikalmen — Thu, 02 Mar 2023 06:58:16 GMT

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)

Re: Variable within Expression within Set Analysis

gaber — Thu, 02 Mar 2023 07:20:19 GMT

Ohh, I was so close - this fixed it!

Thank you, henrikalmen.

topic Re: Variable within Expression within Set Analysis in App Development

Variable within Expression within Set Analysis

Re: Variable within Expression within Set Analysis

Re: Variable within Expression within Set Analysis

Re: Variable within Expression within Set Analysis