Problem with Set analysis date testing

[#1]

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

[m_woolf]

Y0u have mismatched parentheses:

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

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

[CELAMBARASAN]

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]

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

Or

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

[shraddha_g]

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.