2 Replies Latest reply on Aug 22, 2016 9:12 AM by Joshua Milligan

    MAX for Running Totals

    sachin.kambli.0
    sachin.kambli.0 Aug 22, 2016 8:56 AM

      Hi,

       

      I have a view where I calculate running totals of sales for customer Id's for a region. I have a parameter filter, which I can use to say, for e.g., show me 20% of the region sales and it will show me the dollar amount and the number of customer id's it takes to get to 20% of region sales. All this works fine.

       

      What I would like to show in my view is only the region name and the max for the running totals (highlighted in yellow). Basically I want to eliminate the granularity for the customer id;s but my calculations (running totals) are based off of that.

       

      Desired Output:

      Region          Running Sales               % of total Sales                   # of Customer Id's

      Region 1 ==> $21,423,982                  19.28%                                 12 

      Region 2 ==> $149,051,861                18.83%                                 4

      .

      .

      .

      Can somebody please help?

       

      Running Total Pic.PNG

      • 546 Views
      • Tags:
        • 1. Re: MAX for Running Totals
          Joshua Milligan
          Joshua Milligan Aug 22, 2016 9:08 AM (in response to sachin.kambli.0)

          Sachin,

           

          I would suggest using a filter based on Last().  When calculated along Customer ID (Like your other table calcs), Last() will equal 0 for the rows you desire to show.

          Probably, the easiest way is to create a calculated field with code LAST() == 0, make sure it's giving you the results you want (True on the last rows) and then move it the Filters shelf and keep True.

           

          Hope that helps!

          Joshua

          • 2. Re: MAX for Running Totals
            Joshua Milligan
            Joshua Milligan Aug 22, 2016 9:12 AM (in response to sachin.kambli.0)

            Wait, I just realized that won't work because you are already using a table calc filter that's eliminating the last ones.

            So, it gets more complicated!

             

            If you were to add logic to the [% of Region Sales] field that returned null as soon as it is greater than your parameter, then you could create another calculation that checked:

             

            WINDOW_MAX([% of Region Sales]) == [% of Region Sales]

             

            And keep only True

             

            Hope that helps!

            Joshua