Cumulative Period with aggregated measures

[MarcelHaas]

Hi all,

I am using VA7.1 and ran into a feature that seems missing. The short example is the following: Say I have a data set with purchases, one per observation. For a given product (rows in a cross tab) I have defined an aggregated measure: number of times the product was sold, divided by the number of unique customers, so basically it's the average number of sold products per customer. The pros of doing it with an aggregated measure is that it is done "_ByGroup_" so if i make a date hierarchy it will nicely adapt to just the number of sold products and number of unique customers in that period, and the same for combinations of products etc.

Now what I want to do is, from the start of 2009 see the evolution of this number, when I include more and more time (so basically, what is this average if I average from 1-1-09 to t, where t is a date variable beyond 1-1-09). The CumulativePeriod function does something like this for you, but doesn't seem to take aggregated measures as an input. It is on the other hand quite well possible to investigate this by hand, just by only including the years you wnat to know and making a total in the crosstab. This will mean that averaging over 1, 2, 3, 4, 5 or 6 years witll be 6 steps by hand, and then doing something with those numbers.

Is there any solution in VA that could make this work, so I can make a graph that shows if the number converges to a long-era average, or if it is ever increasing? Thanks in advance for any help!

Cheers, Marcel

[WouterHordijk]

Given that each row is a purchase, the number of products sold is represented by the sum of 'Frequency'. If you divide this sum by the distinct count of customer IDs, to identify the number of unique customers, you will end up with the average number of products sold per customer. A solution might thus be the following aggregated measure:

CumulativePeriod(_Sum_, {'Frequency'n}, {'date'n}, _Inferred_, _ByYear_, 0, _Full_, {Date}) / CumulativePeriod(_DistinctCount_, {'customer_id'n}, {'date'n}, _Inferred_, _ByYear_, 0, _Full_, {Date})

[e_graham]

Hello, do you know whether this can be done using time of day also?

cheers,

Ella