8 Replies Latest reply: Aug 27, 2015 8:14 AM by Andrea Stecca

# Loyal customer per years using indirect set analysis

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

Andrea

• ###### Re: Loyal customer per years using indirect set analysis

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?

• ###### Re: Loyal customer per years using indirect set analysis

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

• ###### Re: Loyal customer per years using indirect set analysis

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

• ###### Re: Loyal customer per years using indirect set analysis

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

• ###### Re: Loyal customer per years using indirect set analysis

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

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

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

sales)

• ###### Re: Loyal customer per years using indirect set analysis

what i wanted is  tresesco solution.

• ###### Re: Loyal customer per years using indirect set analysis

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

• ###### Re: Loyal customer per years using indirect set analysis

Thank a Lot !!! is exactly what i want

Thank again