If Statement Set analysis

[pgalvezt]

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!

[dplr-rn]

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]

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]

Sorry I don't understand. 

[Kushal_Chawda]

I don't see two dates in your above script

[pgalvezt]

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).