Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Set analysis date testing

Hello everyone,

did someone can translate this simple sql query to a set analysis expression :

Select sum (amount) from table1

where date1 <= date2

and code in ("AA", "BB", "CC") group by Id

we have already test this expression :

=Sum({<code= {"AA", "BB", "CC"},, Date1 = {$("<=(Date2")}>}(amount))

But the returned result is always null

Thank you

4 Replies
m_woolf
Master II
Master II

Y0u have mismatched parentheses:

=Sum({<code= {"AA", "BB", "CC"},, Date1 = {$("<=(Date2")}>}(amount))

You also have ",," and unnecessary parens around amount

CELAMBARASAN
Partner - Champion
Partner - Champion

It needs a Unique ID from Table 1

Sum({<UniqueId={"=date1<=date2"}code= {'AA', 'BB', 'CC'}>} amount)

OR

Sum({<code= {'AA', 'BB', 'CC'}>} If(date1<=date2,amount))

Anonymous
Not applicable
Author

=Sum({$<Date1 = {(“<=$(=Date2)”}, code= {"AA", "BB", "CC"}>} amount)

Or

=Sum({$<Date1 = {(“<=$(=date(Date2,’MM-DD-YYYY’))”}, code= {"AA", "BB", "CC"}>} amount)

shraddha_g
Partner - Master III
Partner - Master III

if(Date1<=Date2,

sum({<code={"AA","BB","CC"}>}amount),0)

or

sum({<code={"AA","BB","CC"}>} if(Date1<=Date2,amount,0))

Make sure Date Formats for Date1 and Date2 are same.