Set analysis in set analysis

[Janneke]

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.

[#2 · Accepted solution]

Try first sorted value.

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

[sunny_talwar]

Try this expression with CUSTOMER as your dimension:

FirstSortedValue(AMOUNT, -MONTH)

[luizcdepaula]

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

Thanks,

LD

[Janneke]

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