4 Replies Latest reply: Mar 24, 2010 7:57 AM by Peter Cammaert RSS

    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.