Hi. Based on my understanding, these are the steps that I did.
1. Create a cross table. Customers on Rows. Years on Column. Inserted a measure (Units Sold).
2. Selected 2010 and 2011 in the Year Criteria Bar.
3. Create a new calculated column in Targit Advanced Calculations called "Last Year's Customers". The formula is this:
if sum(d-2, 0, m1) <> 0 then 1 else 0This means that if 2010 have sales (not zero) then show as 1, indicating that this customer is also last year's customer. Else, show 0, indicating that they are not last year's customers. Do note that if you are expanding down to Month level, the cell references will change. Quite likely that the new formula will look like
if sum(d1, 0, m1) <> 0 then 1 else 0, assuming January is always the first month of Last Year.
4. Use visibility Agent to hide not last years customers, which are customers with "0" for the calculation.
This can be improved further if you use Targit's Dynamic Periods so that it will always show the latest 2 years.
You can also consider hiding the calculated column "Last Year's Customers", or other non-related months in the last year (if viewing data at the month level).
To create a total or sum of all visible customers, i.e. last year's customers, you can create a calculated row with the following formula:
sum(0, all(v), 0)The following is the output and the working on achieving this.
Output:
Working (in Advanced Calculations, the shaded rows are the hidden rows using Visiblity Agent):
