Set Analysis Expression "-=" operator not working in Qlik sense Version Nov 14.212.7 Patch2

[nagarjuna005]

Hello Team,

I am trying to fix the below set analysis expression "-=" operator not working.

In expression, I want to filter data related to Due amount as highlighted below 

Num(

  Sum({<

    Cleared_Flag={'Unpaid'},

     [%KeyDate]={"$(=max([%KeyDate]))"},

     IsDebitor={"$(vDebitorsOnly)"},

     [Ev_Overdue.Type]={'Overdue'},

     [Ev_Overdue.Bucket]-={'Due'}

    >}

  [Account_Receivable_Evolution.Amount_$(vCurrency)]

  ),

  '# ##0' & '$(vCurrencySymbol)'

)

This works earlier suddenly stopped working kindly help on this issue.

[Oleg_Troyansky]

Hi there,

It's hard to guess what went wrong with your expression without seeing the data.

I find your date condition rather questionable. You are using double quotes, which signify Search, however there is no search condition there - just a single date, calculated within the $-sign expansion. If you are looking for a single date, then drop the double quotes and use it as a simple selection filter. If your dates are numeric, then no quotes are needed. However, if your dates are fully formatted date fields, then I believe you need to add the Date() formatting function and enclose your $-sign expansion in single quotes.

To learn more about advanced Set Analysis, come to my session at the Masters Summit for Qlik this September in Hamburg.

Cheers,

[nagarjuna005]

my goal is to eliminate due amount from the set analysis expression so that Due amount should not include into the report. so i am using the above expression to eliminate but "-=" is not working as expected. thanks for your help

[Oleg_Troyansky]

I understand your goal, but I cannot help you any further without seeing your app. 

[Vegar]

It worked before, but not anymore.

What changed ? I would expect that you either made a change to the data model, the expression or your environment. What has changed?

You ensure it is the modifirer that you pointed out that is the problem. Does it not work , then try to remove all but the troubled modifier from you expression.

[nagarjuna005]

not changed anything related to data model so I am not sure why its not working.

I want to exclude Due amount with in expression so that its not bring into report .

Please provide any alternate solution for this.

Thanks for your help!

[Scotchy]

Check as per below...

Step 1: Check Variable Definitions
Make sure the variables referenced in the expression are correctly defined and returning values:

vDebitorsOnly

vCurrency

vCurrencySymbol

Verify each variable in a simple KPI object to ensure they contain the expected values.

Example:

='Debitors Variable: ' & $(vDebitorsOnly)

Step 2: Date Format and [%KeyDate]
The %KeyDate field might be a numeric or text representation of a date. Ensure that the format returned by:

=$(=max([%KeyDate]))
matches exactly what's stored in your %KeyDate field.

Test in a KPI object:

=$(=max([%KeyDate]))
Make sure this returns a date in the identical format stored in %KeyDate.

If %KeyDate is numeric, check if the max returns numeric.

If %KeyDate is a date string, ensure formatting matches exactly.

Step 3: Field Content and Data Model Changes
Check if the values exist and align properly:

Cleared_Flag={'Unpaid'}

[Ev_Overdue.Type]={'Overdue'}

[Ev_Overdue.Bucket]-={'Due'}

Make sure these values still exist in the dataset. Perhaps values were changed or normalized differently in recent data loads.

Simple check:

=Count({<Cleared_Flag={'Unpaid'}>} DISTINCT Cleared_Flag)
=Count({<[Ev_Overdue.Type]={'Overdue'}>} DISTINCT [Ev_Overdue.Type])
=Count({<[Ev_Overdue.Bucket]-={'Due'}>} DISTINCT [Ev_Overdue.Bucket])
These should return numbers greater than zero if data exists.

Step 4: Currency Field Name Changes
Verify the field [Account_Receivable_Evolution.Amount_$(vCurrency)] exists.

Maybe vCurrency changed to a value for which no field currently exists (e.g., it switched from USD to AUD).

Confirm in a KPI object:

=Count([Account_Receivable_Evolution.Amount_$(vCurrency)])
If this returns a number, the field exists. If it returns an error, check field names in the Data Model Viewer.

Quick Diagnostic Test:
Replace the whole expression temporarily with a simpler one to confirm set conditions work:

=Sum({<Cleared_Flag={'Unpaid'}>} [Account_Receivable_Evolution.Amount_$(vCurrency)])
Then gradually add conditions back one at a time, verifying each step.

Most Common Issue
Typically, such an expression stops working because:

The date returned by max([%KeyDate]) no longer matches the stored date format.

One of the variables (vDebitorsOnly, vCurrency) changed or became empty.

The dataset changed (field renamed, cleared flags or overdue types altered).

Suggested Next Steps:
Confirm variable values (Step 1)

Check the output of max([%KeyDate]) (Step 2)

Check data model integrity (Steps 3 & 4)

Simplify and rebuild the set analysis to isolate the problem

Following this methodical approach will pinpoint and resolve the issue efficiently.

[Anil_Babu_Samineni]

I remember the issue when I face after hotfixes one day 🙂

Can you please check how the Due looks like in your data model (I mean, what is the ANSI keyword?)?

Can you do Favor checking this in Qlik server (Good to check engine servers).

Using PowerShell as Admin and Run the command Get-Hotfix and share the screenshot.

[marcus_sommer]

Before looking for any expression-issues take a look on the real existing data with the relevant fields within a table-box.

[krishna20]

Hi @nagarjuna005 ,

Just do a basics checks of your expression using table and text objects, what is the exact value that you are getting in each parameter or attributes.

Cleared_Flag={'Unpaid'},

     [%KeyDate]={"$(=max([%KeyDate]))"},

     IsDebitor={"$(vDebitorsOnly)"},

     [Ev_Overdue.Type]={'Overdue'},

     [Ev_Overdue.Bucket]-={'Due'}

    >}

  [Account_Receivable_Evolution.Amount_$(vCurrency)]

  ),

  '# ##0' & '$(vCurrencySymbol)'