topic Re: Change If Then to Set Analysis using Dates in QlikView

Change If Then to Set Analysis using Dates

Tue, 18 Mar 2014 19:42:22 GMT

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!!

Re: Change If Then to Set Analysis using Dates

alexandros17 — Tue, 18 Mar 2014 19:57:04 GMT

Sum({$ <SaleDate={'>=$(DateEffStart)<=$(DateEffEnd)'},SaleDate={'>=$(vStartDate )<=$(vEndDate )'}> Amount}

Re: Change If Then to Set Analysis using Dates

Anonymous — Tue, 18 Mar 2014 20:04:29 GMT

Try this (check the parenthesis):

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

Re: Change If Then to Set Analysis using Dates

Tue, 18 Mar 2014 20:39:13 GMT

When I plugged in the expression, everything up to the * was fine, but something was wrong with the rest. (there was a red syntax underline under everything after the *< .

Is something slightly off there? Thanks very much!

Re: Change If Then to Set Analysis using Dates

Tue, 18 Mar 2014 20:45:06 GMT

I tried this and it summed up all values for every row. Didn't seem to pay any attention to any of the date delimiters.

Any other ideas? Thanks you!

Re: Change If Then to Set Analysis using Dates

Anonymous — Tue, 18 Mar 2014 20:47:30 GMT

The syntax red lines are often misleading, especially in sets. But possibly I mistyped something. Anyway, try these two parts separately first:

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> Amount)

and

sum({<SaleDate={"$(='>='&date($(vEndDate))&'<='&date($(vEndDate)))"}> Amount)

just to check if the both parts are ok.

Re: Change If Then to Set Analysis using Dates

Tue, 18 Mar 2014 20:59:41 GMT

They the saledate between the start and end dates works fine.

The Saledate between the effective dated start and end just totals up all values for everyone. But it does give a result.

Re: Change If Then to Set Analysis using Dates

Anonymous — Wed, 19 Mar 2014 12:05:10 GMT

In this case this should give a result (I certainly mistyped in my earlier post, wrong copy/paste 😞

sum({<SaleDate={"$(='>='&date(DateEffStart)&'<='&date(DateEffEnd))"}> * <SaleDate={"$(='>='&date($(vEndDate))&'<='&date($(vEndDate)))"}>} Amount)

The fact that result "between the effective dated start and end just totals up all values" doesn't tell much without seeing the application. The syntax may be correct but logic may be not...

Regards,

Michael

Re: Re: Change If Then to Set Analysis using Dates

Wed, 19 Mar 2014 14:21:59 GMT

Thanks Michael. No luck. I modified the test file so I can upload it. I know that makes it a LOT easier so here it is. Thank you again for looking at this for me.

Re: Re: Change If Then to Set Analysis using Dates

Anonymous — Wed, 19 Mar 2014 14:32:36 GMT

I'm not good in copy/pasting

Notice that in the second part I used vEndDate twice. Just replace the first one with the vStartDate:

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

Re: Re: Re: Change If Then to Set Analysis using Dates

Wed, 19 Mar 2014 14:41:51 GMT

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

Re: Change If Then to Set Analysis using Dates

Anonymous — Wed, 19 Mar 2014 15:17:08 GMT

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.

Re: Re: Re: Change If Then to Set Analysis using Dates

Wed, 19 Mar 2014 18:25:51 GMT

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

Re: Re: Re: Change If Then to Set Analysis using Dates

Anonymous — Wed, 19 Mar 2014 18:41:41 GMT

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