turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- Cumulative Period with aggregated measures

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2015 03:40 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to MarcelHaas

02-19-2015 10:32 AM

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})

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to WouterHordijk

04-12-2015 10:02 PM

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

cheers,

Ella