Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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
So is it those customers who are common in both the years?
In the exemple customer common in 2014 and 2013 are : A1 , B1 , E1
How about this expression:
Sum({<Year = {$(=max(Year))}> *
<Customer = P({<Year = {$(=max(Year))}>} Customer)> *
<Customer = P({<Year = {$(=max(Year)-1)}>} Customer)>}
sales)
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
Thank a Lot !!! is exactly what i want
Thank again
Thenk for your help Jonathan,
what i wanted is tresesco solution.