Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How about
Sum({<duedate={">=$(=Only(DATE1)) <=$(=Only(DATE2))"},type={'commission_fee'}>}amount)
?
How about
Sum({<duedate={">=$(=Only(DATE1)) <=$(=Only(DATE2))"},type={'commission_fee'}>}amount)
?
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.
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