4 Replies Latest reply: Feb 17, 2017 1:15 AM by Shraddha Gajare

# 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

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

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

• ###### 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.