topic Re: Set analysis and two dates Fields in QlikView

Set analysis and two dates Fields

Thu, 05 Jun 2014 13:01:23 GMT

Hi,

I've got in my module,

the following tables:

Item Table:

Item,

Date

Sales:

Item,

SaleDate,

Qty

I need to calculate for each item sum(Qty) where SaleDate<= Date.

I know set analysis back and forward but somehow i can't manage to do that.

Can someone know how to solve this issue?

Thnx a lot

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:04:36 GMT

sum({<SaleDate = {"<= Date"}>} Qty)

let me know if this works for you.

Re: Set analysis and two dates Fields

tresB — Thu, 05 Jun 2014 13:06:07 GMT

sum({<SaleDate ={ "<= Date"}>} Qty)

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:06:09 GMT

See the set analysis used in the tables in the attached file.

Re: Set analysis and two dates Fields

bbi_mba_76 — Thu, 05 Jun 2014 13:08:35 GMT

Hi,

did you try using an if ?

sum(if( SaleDate<=Date,Qty))

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:12:34 GMT

Great & Simple

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:15:00 GMT

While the solution works, if statements produce slower results than set analysis.

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:18:23 GMT

I dont know if im missing somethign here, but you dont need set analysis. You just need an if statement in the expr =sum(if(SalesDate<=Date,Qty)). Please check the attached.

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:19:53 GMT

Yes both solutions will work, but if statements produce slower results than using set analysis. It is optimal to use set analysis over if statements whenever possible

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:22:42 GMT

I know set analysis is quicker,

still didn't get right syntax for set analysis that works for that.

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:24:19 GMT

Thanks for the help,

but I've got two date fields, so i need to compare them without variables

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:26:45 GMT

I understand.

So your expression would be: sum({<SalesDate = {"<=Date"}>}Qty)

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:28:57 GMT

You cannot compare two different fields in set analysis. You have to use if.

Re: Set analysis and two dates Fields

Thu, 05 Jun 2014 13:31:11 GMT

tried it and similar ups and down without a success

Re: Set analysis and two dates Fields

Tue, 10 Jun 2014 08:39:35 GMT

Does anyone knows how to deal the issue in the script itself?

mainly because user perfomance