topic Re: Set Analysis in QlikView

Set Analysis

Wed, 10 Feb 2016 13:01:21 GMT

Hello All

I could not solve writing the expression for this problem, need ur help

Data:

Customer,Date, Sale1, Sale2

C1, 01/03/2014, 0,1

C1,01/10/2014,0,1

C1,01/25/2014,0,2

C1,03/02/2014,1,2

C1,04/01/2014,2,1

For each Customer, find the 1st Sale1 Date and then sum of Sale2 before this date.

ex: 1st Sale1 is on 03/02/2014 , i got to show sum(1+1+2) ...

Re: Set Analysis

Wed, 10 Feb 2016 14:48:23 GMT

Try this

sum({<Date=P({<Sale1={"<=0"}>} Date)>} Sale2)

Re: Set Analysis

t_chetirbok — Wed, 10 Feb 2016 14:58:04 GMT

Hello!

Try this expression: sum({<Date={"<$(=date(min({<Sale1-={0}>}Date)))"}>}Sale2)

Re: Set Analysis

sebastianlettner — Wed, 10 Feb 2016 15:00:48 GMT

Hi,

this should work

=Sum({<Date={"<$(=Min({<Sale1=-{0}>} Date))"}>} Sale2)}

Regards Sebastian Lettner

Re: Set Analysis

Wed, 10 Feb 2016 15:05:19 GMT

Min date got to be dynamic with the customer. each customer will have diff min date,i have not tried your exp but i assume $() sign will expand the exp before the calculation in the chart which makes it one min date for the entire table.

Re: Set Analysis

sebastianlettner — Wed, 10 Feb 2016 16:33:05 GMT

Hi,

do you need this to be done with set analysis?

If not, you could use this expression:

Sum(Aggr(If(Min(total<Customer>{$ <Sale1-={0}>}Date) <= Date, Sale2), Date, Customer)).

Regards
Sebastian Lettner

Re: Set Analysis

t_chetirbok — Thu, 11 Feb 2016 07:05:16 GMT

Hi! It's work perfectly! ))

try to use, if it's not work, share your example, please.