How to compare current year to previous year for succesive years?

[Peter_Cammaert]

Hi all,

I have this (simplified) table of transactions with CustomerID, OrderID, Date and Amount. The table contains records for the last ten years.

For each year, I would like to count the number of distinct CustomerIDs that didn't generate any business that year (sum(Amount) zero or Null), but that did business during the previous year, e.g. counting lost customers for each year. The inverse is the number of new customers for every year.

I tried around ten different variations on three themes:

• Set analysis: no luck, cannot vary the set with the value of a dimension, as detailed in different forum posts
• Aggr() nesting: no luck, I don't seem to be able to produce an expression that works
• Duplicate table in load script: got lost in unconnected data, not producing any usable results

Does anyone have a suggestion about how this can be accomplished? Thanks.

Peter

[#2]

Hi Peter

If you create a loosen table with the years

load

distinct

year

(Date) as SelectedYear

resident

You can use this to calculate

Table;

count

(if(year(Date) = SelectedYear -1,CustomerID))

-

count

(if(year(Date) = SelectedYear ,CustomerID))

Anders

[Peter_Cammaert]

Ok, that works if we were going to select a specific year before showing the results. I guess that would work with set analysis as well, as there is only one year to show at a time.

Unfortunately, the end result should be a chart with Year as a dimension and Lost customers as an expression.

Peter

[#4]

Hi Peter

You can use SelectedYear as a dimension and the expression in a chart.

Anders

[Peter_Cammaert]

Hi Anders,

your code works if I add 'distinct' in each count(). So far so good.
However, this function only plots the variation in number of customers (#customers CY - #customer LY), not the amount of customers lost. Your solution will return 0 for a year when 5 existing customers are replaced with 5 new customers. But actually, the data point should be -5.

IMHO that makes it a lot more complex, don't you think?

Peter