Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Y0u have mismatched parentheses:
=Sum({<code= {"AA", "BB", "CC"},, Date1 = {$("<=(Date2")}>}(amount))
You also have ",," and unnecessary parens around amount
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))
=Sum({$<Date1 = {(“<=$(=Date2)”}, code= {"AA", "BB", "CC"}>} amount)
Or
=Sum({$<Date1 = {(“<=$(=date(Date2,’MM-DD-YYYY’))”}, code= {"AA", "BB", "CC"}>} amount)
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.