qliksense table not filter the set expression condition

[ramakrishna124]

I am migrating qlikview to qliksense

this is qlikview expression

If(Currency='USD',

    SUM({$<[AA.PostingDate] ={"<=$(=[Ageing Filter])"}>} 

if([Ageing Filter] - [AA.DueDate]<1 AND [Ageing Filter] - [AA.DueDate]<-30,AA.AmountUSD)),

(SUM({$<[AA.PostingDate] ={"<=$(=[Ageing Filter])"}>} 

if([Ageing Filter] - [AA.DueDate]<1 AND [Ageing Filter] - [AA.DueDate]<-30,AA.AmountLCY))/MAX({<DateNum={$(EndDate)}>} FX1.USDRate))

)

in qlikview is working properly 

i rewrite the code as below

If(Currency='USD',

  SUM({$<[PostingDate] ={"<=$(sfilter)"}>},  

if($(sfilter) - [AA.DueDate] <1 AND $(sfilter) - [AA.DueDate]<-30, AA.AmountUSD))

,

SUM({$<PostingDate] ={"<=$(sfilter)"}>}  

if($(sfilter) - [AA.DueDate] <1 AND $(sfilter)- [AA.DueDate] <-30,AA.AmountLCY))

    /MAX({$<DateNum={"$(sfilter)"}>} FX1.USDRate)

 )

above sfilter is the variable of user selected date in  num format but it not filtering the condition (<=$(sfilter)) starting date to posting date instead it return all the row (postingdate) in the table . it is not applying the filter completely.

[Vegar]

When writing a expression in QS you often see a kind of preview of the measure in the bortom of that expression input box. Does that preview look as you would expect? If not then adress those issues.

It is har do be specific not seeing your variable definition. Is it a static value or a field name or an expression?

If it is a field name, does the field name have spaces in it? If so then try to add square brackets around the dollar expansion, like this: [$(sfilter)]

[Scotchy]

G'day,

I'm reviewing ChatGPT 4.1 at present so thought your question would be a candidate for testing it's code capabilities. Below is a response from the model... Let me know if it fits/works for you...

---

Summary of Issues Migrating from QlikView to Qlik Sense

1. Set Analysis Syntax:

   • In your expression, the set analysis brackets were sometimes missing or misplaced ([PostingDate] instead of <[PostingDate]>).

2. Variable Expansion:

   • In Qlik Sense, variables in set analysis need $(=sfilter) for numeric/date variables, not $(sfilter) (which just expands as a string).

3. Conditional Logic:

   • The original logic was impossible:
     if(X < 1 AND X < -30, ...)
     A value can’t be less than both 1 and -30 at the same time. Most likely you meant “between -30 and 1”:
     if(X < 1 AND X > -30, ...)

4. Set Analysis Only Pre-filters:

   • Remember: set analysis selects records before calculation.
     Your if() conditions further filter which records are summed but only within the set.

Corrected Qlik Sense Expression

See attached formatted text