Change If Then to Set Analysis using Dates

[#1]

Any idea how to change this (Functioning)  If/Then into set analysis:

=sum(If(SaleDate>=DateEffStart and SaleDate<=DateEffEnd  //Sale date of the item has to be within the effective dated start and end

   and SaledDate >= vStartDate and SaleDate <= vEndDate, Amount))  //and the saledate also has to be within the input start/end date variables.

So SaleDate, DateEffStart and DateEffEnd are all fields in the database.   SaleDate is the date of the sale.

Thanks very much!!

[#2]

I see that.  I made the change, no luck.  The picture above shows the expression with this last set analysis.

The second picture shows what it should look like, which is with the If Then....

[Anonymous · Accepted solution]

This is what I meant earlier "The syntax may be correct but logic may be not..."

You're using this expression in the chart, assuming that the rows are aggregated per dimensions.  But set modifiers doesn't know anything about dimensions, they look at the overall data set.

Doesn't look to me as a place for set analysis.

[#4]

Well this just confirms what I thought then, but thanks again for the follow up.  I hoped I was just hitting a syntax error but I guess it was a bigger issue.   I am using If Then with an aggr on a huge data set, so was hoping to eliminate one or the other without making a big investment in time to revise the data structure in some way.  But in order to deal with the slowness of both It/Thens and aggr in QlikView I will have to come up with some creative workarounds.  Too bad QlikView can't get it/thens and aggr to work faster.  Maybe in Qlik Next they will perform better!  Fingers crossed.

Thanks again,

Richard

[Anonymous]

If the issue is the performance, the best solutions are usually on the data model level.  The price of course is the flexibility - whatever is aggregated in the script, can't be "un-aggregated" on the front end.  Looks like in your case you must have it on the front end.

Speaking of the better performance of set analysis...  It may perform better but the calculation wit sets are not cached, so again you win some - you loose some.

If this is any help, here is one more expression, mixing set and if:

=sum({<SaleDate={"$(='>='&date(vStartDate)&'<='&date(vEndDate))"}>} if(SaleDate>=DateEffStart and SaleDate<=DateEffEnd, Amount))

Variables are independent from dimensions, so it's OK to use here in set.  No idea what the performance of this combo will be.

Regards,

Michael