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
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pgalvezt
Specialist
Specialist

If Statement Set analysis

Hi,

I need this as a Set Analysis:

sum(if(Date>=Date1
and Date<=Date2, Field))

I try this but doesn't work,

Sum({<Date={">=$(=Only(Date1)) <=$(=Only(Date2))"}>}Field)

Thanks!

6 Replies
dilipranjith
Partner
Partner

are all 3 date fields columns in your data model?

 

if possible create flag which satisfy the condition in script and then use that flag in the expression. this should give best performance

Sum({<DateCheckFlag={1}>}field)

alternatively with just set analysis if so you need to do something like below. thats the syntax for comparing 2 columns

Sum({<ID={"Date>=Date1"}>}Field)

 

Kushal_Chawda

try this

=sum({<Key={"=Date>=Date1 and Date<=Date2"}>}Field)

Here Key  is dimension i.e unique identifier of your table or chart. If you have multiple dimensions as unique identifier of your table then create create concatenated field and use it here in set

pgalvezt
Specialist
Specialist
Author

Hi,

There are 2 dates in my table (Date1 and Date2)

Date is:

FactTable:
LOAD * INLINE [
Date, Sales
01-01-2013,93
02-01-2013,62,
08-01-2013,39,
09-01-2013,94
10-01-2050,63];
TableMinMax:
LOAD
min(Date) as DateMin,
max(Date) as DateMax
Resident FactTable;
LET vMin=num(Peek('DateMin',0,'TableMinMax'));
LET vMax=num(Peek('DateMax',0,'TableMinMax'));
MasterCalendar:
LOAD
Date(IterNo()+$(vMin)-1) as Date,
Year(Date(IterNo()+$(vMin)-1)) as Year,
Month(Date(IterNo()+$(vMin)-1)) as Month,
Day(Date(IterNo()+$(vMin)-1)) as Day

AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);
DROP Table TableMinMax;

LET vMin=;
LET vMax=;

 

pgalvezt
Specialist
Specialist
Author

Sorry I don't understand. 

Kushal_Chawda

I don't see two dates in your above script

pgalvezt
Specialist
Specialist
Author

I'm Sorry maybe I don't explained correctly.

Date is another table, where I use it in my IF Statement (fact Table - Master Calendar),

Date 2 and Date 3 is on another table (System Table).