Loyal customer per years using indirect set analysis

[Anonymous]

Hello,

I' m try to have a pivot table whit my loyal customer by year with the sum of amount by years.

my dimension are : Customer and Year

my expression is :

sum ( {<Customer=p({<Year={'<$(=max(Year))','<$(=max(Year)-1)'}>}Customer)>} sales)

The problem is when i select year 2014 the pivot table not show me also year 2013

See attchament

Thanks in advance for your help

Andrea

[tresesco]

Year filter is not ignored in the main(outer) set expression, hence it shows only the selected year data. What are you actually trying to achieve? P() should be used if you wish to get all sales for the customers who have participated in 2014,2013. And mind it, if a customer A is there in sale either in 2015 or 2014 ... all it's sales even beyond 2014 would come up with p(). Do you want that?

[Anonymous]

I would like customer both selling in 2014 and 2013 divede by year like for exemple and ignore all other

customer that was selling or in 2014 and not in 2013  or 2013 but not in 2014

like this

customer          2014          2013

A1                    100          20

B1                    50             10

and let the user decide witch year want the comparate 

[tresesco]

So is it those customers who are common in both the years?

[Anonymous]

In the exemple customer common in 2014 and 2013 are : A1 , B1 , E1

[jonathandienst]

How about this expression:

Sum({<Year = {$(=max(Year))}> *

  <Customer = P({<Year = {$(=max(Year))}>} Customer)> *

  <Customer = P({<Year = {$(=max(Year)-1)}>} Customer)>}

sales)

[tresesco · Accepted solution]

Like this?

Sum({<Year={'<=$(=Max(Year))>=$(=Max(Year)-1)'},Customer=p({<Year={'$(=Max(Year))'}>})>*<Year={'<=$(=Max(Year))>=$(=Max(Year)-1)'},Customer=p({<Year={'$(=Max(Year)-1)'}>})>}sales)

PFA

[Anonymous]

Thank a Lot !!! is exactly what i want

Thank again

[Anonymous]

Thenk for your help Jonathan,

what i wanted is  tresesco solution.