less than or greater than Date in SET Analysis

[tomovangel]

Hello dear friends,

I have been trying the past few hours to implement a simple Date formula in Set analysis.

My condition is to get the sum(amount) between 2 dates and a condition based on a Type field.

So that's 3 conditions in the set analysis.

Fields used:

type- field in the script

DATE1-field in the script

DATE2- field in the script

duedate- field in the script

amount- field in the script

I have tried the following formulas, none of which works:

1. Sum({<duedate={">=(=Date(DATE1)) <=(=Date(DATE2))"},type={"commission_fee"}>}amount)

2. Sum({$<type={'commission_fee'},duedate={">=DATE1"}>},duedate={"<=DATE2"}>} amount)

3. Sum({$<duedate={">=DATE1 <=DATE2"},type={"commission_fee"}>}amount)

4. Sum({$<duedate={"'>=DATE1 <=DATE2'"},type={"commission_fee"}>}amount)

And more...

PS: duedate,DATE1,DATE2 - have been formated using Date(fieldname) in the loadscript, so they have the same format.

I have done this before, but I can't remember how exactly was the syntax, any help will be highly appreciated

[juraj_misina · Accepted solution]

How about

Sum({<duedate={">=$(=Only(DATE1)) <=$(=Only(DATE2))"},type={'commission_fee'}>}amount)

?

[vamsee]

As Date1 and Date2 are field's in the script they might have multiple values. for that purpose add max or min.

sum({<duedate={">=$(=(Date(Max(DATE1))))<=$(=Date(Max(DATE2)))"} ,type={'commission_fee'}>}

amount)

Also, use single quotes if you are searching for a text. Double quotes are used to execute the set.

[tomovangel]

Okay, that works great  

I have just gotten it to work in a different format like

Both work, juraj.misina‌'s sollution and this one:

Sum({<type={"commission_fee"},duedate={">=$(=Date(DATE1))<=$(=Date(DATE2))"}>}amount)

If anyone in the future sees this, you can use both