Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

less than or greater than Date in SET Analysis

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

1 Solution

Accepted Solutions
juraj_misina
Luminary Alumni
Luminary Alumni

How about

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

?

View solution in original post

3 Replies
juraj_misina
Luminary Alumni
Luminary Alumni

How about

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

?

vamsee
Specialist
Specialist

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
Partner - Specialist
Partner - Specialist
Author

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