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

MAX for Running Totals

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

.

.

.

• 1. Re: MAX for Running Totals

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

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