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

    MAX for Running Totals




      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

        • 1. Re: MAX for Running Totals
          Joshua Milligan



          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!


          • 2. Re: MAX for Running Totals
            Joshua Milligan

            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!