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

    MAX across a dimension which is not shown

    Martin Smrt

      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
          . Indumon

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

           

          screen.jpg

           

          Please let us know if your requirement is more complex ?

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

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