Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
mwoolf
Honored Contributor II

Re: Problem with Set analysis date testing

Y0u have mismatched parentheses:

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

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

Re: Problem with Set analysis date testing

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))

galax_allu
Valued Contributor

Re: Problem with Set analysis date testing

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

Or

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

shraddha_g
Honored Contributor III

Re: Problem with Set analysis date testing

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.