2 Replies Latest reply on Nov 1, 2013 10:30 AM by Martin Smrt

# MAX across a dimension which is not shown

I'm working on a report which looks at recurring purchases by cohorts based on sign-up date. The data looks like this:

CountrySign-up datePurchase dateUnique user purchasesRevenue
USJanuaryJanuary301000
USJanuaryFebruary15600
USJanuaryMarch10200
USFebruaryFebruary401500
USFebruaryMarch301000
USMarchMarch20700
CAJanuaryJanuary390
CAJanuaryFebruary00

What I need to get is:

Sign-up dateSign-ups (users)Total Revenue
January331890
February402500
March20700

While the Total Revenue is a plain SUM, the user count is a MAX across all purchase dates. I need to understand how many users initially signed up and how much money they spent in total.

Hope this is clear. Any ideas on how to do this, please?

Message was edited by: Martin Smrt

• ###### 1. Re: MAX across a dimension which is not shown

Hi Martin,

It is possible to show max across a dimension which is not shown, using a Table calculation (Lookup). However I don't think that is  required to get MAX user count  across all purchase dates, because this can be achieved by a simple MAX function.

I just created a calculated field Max Unique Purchase= Max(Unique user Purchases) .

1 of 1 people found this helpful
• ###### 2. Re: MAX across a dimension which is not shown

Hi,

I realized I didn't explain the full extent of the challenge. I edited the original question.

There's one more dimension, e.g. Country. Neither Country nor Purchase date will be shown in the report.

I need to get the number of users which is basically a SUM of all (MAX(Unique user purchases) across all dates) for each country. Hope this is clear :-)