5 Replies Latest reply on Sep 23, 2019 6:23 AM by Jim Dehner

# Using Running sum to find out when the benchmark was achieved

Need support with an issue

I have sample data attached at CustomerID, OrderDate & Revenue

I want to construct a LOD calculation that allows me to see how many days have elapsed before the Customer hit a million dollars in revenue.

Example Customer 5012 crossed million dollars on Day 157 whereas customer 8680 crossed on Day 31

• ###### 1. Re: Using Running sum to find out when the benchmark was achieved

Good morning

You already have a way to a solution in mind - unfortunately LOD's are not going to get you where you want to go - you will need to use a table calculation to do a running total and check against the target

Give it a try and when you run into trouble post your TWBX workbook so we can see where you had a problem

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Using Running sum to find out when the benchmark was achieved

I have a sample workbook in twbx format attached

I created a Days Elapsed and a Threshold formula. Basically I want to see the first "Days elapsed" when the threshold becomes from 0 to 1. I was writing a Fixed LOD as the following

{FIXED [Customer ID] : Min(

(if [Threshold] = 1 then [Days Elapsed] end)

)

}

But I am getting the "Cannot mix aggregate and non aggregate measures" error

• ###### 3. Re: Using Running sum to find out when the benchmark was achieved

you are real close - see the attached

the running sum is a table calc - so you have to have a table underlaying the worksheet - but you can hide values on the worksheet

I added a calculation to find the point where the running total goes over 1000000

it could be anything - I just wanted it very visable

and you add it to the viz and in the table it finds the point

now

You can add a filter like this  - you get the ROW that Precedes the 1000000 mark

and you get this

you can hid anything you don't want to see

to get something like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Using Running sum to find out when the benchmark was achieved

Thanks a ton for solving this ! I was stuck at the filter option, that is a neat trick !

I am trying to see my options on the production database, that has around 14 million rows so the Table calculations are taking a long time to render.

Regards,

Ujval

• ###### 5. Re: Using Running sum to find out when the benchmark was achieved

with 14 million rows it could take some time

Glad to help out

Jim