topic Re: Set analysis in set analysis in QlikView

Set analysis in set analysis

Janneke — Tue, 31 May 2016 07:55:59 GMT

Hi all,

I have a question regarding set analysis within set analysis. Hope that you can help me.

The data I am using has amounts per customer, stamped every month. For instance:

CUSTOMER	MONTH	AMOUNT
C-01	201507	6
C-01	201508	12
C-01	201509	18
C-02	201512	100
C-03	201511	20
C-03	201512	40

I would like to have a table that shows customer and the last amount, given a certain month. For instance, if I choose 201512:

CUSTOMER	AMOUNT
C-01	18
C-02	100
C-03	40

I have come up with this solution:

=Sum({$<Indicator={$(vIndicator)},Type={'Realisation'},YEARMONTH={"$(=Max({$<Indicator={$(vIndicator)},Type={'Realisation'},YEAR={$(vTHIS_YEAR)},MONTH={'<=$(vTHIS_MONTH)'}>}YEARMONTH))"}>}AMOUNT)

Problem with that is that it only shows values for the customers that have 201512 as the last available month:

CUSTOMER	AMOUNT
C-01	0
C-02	100
C-03	40

This makes sense, since I don't break down to customer. However, I don't know how and where to add the customer to the expression.

Can you please help me find the right syntax for the last step?

Regards, Janneke.

Re: Set analysis in set analysis

Tue, 31 May 2016 18:38:02 GMT

Try first sorted value.

SUM(Firstsortedvalue({<set analysis for amount greater than 0>} amount, -Month))

Re: Set analysis in set analysis

sunny_talwar — Tue, 31 May 2016 18:43:48 GMT

Try this expression with CUSTOMER as your dimension:

FirstSortedValue(AMOUNT, -MONTH)

Re: Set analysis in set analysis

luizcdepaula — Mon, 18 Jul 2016 15:30:13 GMT

What is the ({<set analysis for amount greater than 0>}?

Thanks,

Re: Set analysis in set analysis

Janneke — Tue, 19 Jul 2016 07:23:53 GMT

It is just the set analysis that you want to use. Nothing particular.