Visible current year figures, selection previous year dimension

[Michiel_QV_Fan]

Hi all,

I have to display the following:

Current year sales figures.

and

Current year sales figures filtered by the sum of sales by customer from previous years.

In the script I created a group by calcuation to get the sum Sales per year by customer.

I have trouble finding the correct way to select the customer from previous year to display the figures from the current year.

A customer in the previous year with sales over $ x = keyaccount.

This result is needed to determine the current year keyaccount customer sales. In other words, a keyaccount in 2010 should define the displayed customers in 2011.

Thanks for your help.

[swuehl · Accepted solution]

I think you should be able to do this with set analysis, maybe along this line

=sum( {$<CustomerID = {“=Sum({1<Year = {2010} >} Sales ) > 100000”}>} Sales )

returns the sales for current selection, but with a new selection in the CustomerID field: only customers who during 2010 had a total sales of more than 100000.

if you want the last year to be dependent on Year selection, you could try

=sum( {$<CustomerID = {“=Sum({1<Year = {'$(=max(Year)-1)'} >} Sales ) > 100000”}>} Sales )

Put this expression in a table chart with dimension CustomerID.

Hope this helps,

Stefan

[Michiel_QV_Fan]

Thanks.

Of course Set Analysis. .....

I changed max(Year) to only(year)