6 Replies Latest reply on Nov 1, 2018 8:40 AM by Ken Flerlage

# Finding the number of occurances in a column

Hello everyone,

I have the chart you see below. What I wanna know is how many of the x's you see are in the range of 100-80%, 80-60% etc. I've been trying for some time now but I really can't figure out how to do it. I've attached the file.

• ###### 1. Re: Finding the number of occurances in a column

I'm thinking there may be a more elegant way to do this, but this should work.

First create an LOD that basically determines whether a mark is in the specific range. I'm only showing 80-100, but you'd need these calculations for all of your ranges:

Count 80-100

// Is the mark in the given range.

// %BeforePlan is already an aggregate, so we're using an LOD...

// ...to sort of trick it into no longer being an aggregate.

{FIXED [date], [driver]:

IF [%BeforePlan]>.8 and [%BeforePlan]<=1 THEN

1

ELSE

0

END

}

Total 80-100

// Sum the marks in the 80-100 range.

{FIXED : SUM([Count 80-100])}

Attached is a workbook with 5 different ranges. And I've added a sheet with the counts for each range.

• ###### 2. Re: Finding the number of occurances in a column

Good morning

see the attached

try this

computed as shown

it returns 48 on the first and 52 on the 2nd

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.

• ###### 3. Re: Finding the number of occurances in a column

Hi Ken,

Thank you for you quick response. I want to be able to monitor this on a day to day basis. Doesn't seem I can do that with this. And I can't see your workbook, seems to be made with a newer version build even though I have the latest version.

• ###### 4. Re: Finding the number of occurances in a column

Hi Jim,

Thanks for your quick response too. This just gives me the number of drivers on that specific day.

• ###### 5. Re: Finding the number of occurances in a column

Maybe my question wasn't complete enough. What I wanna see is the following:

- each row in the data source is a stop of the driver with the planned time and the real time.

- I wanna see how many stops of the driver on a given day have been before the planned time.

- This results in a percentage per driver per day, how much of his stops were before the planned time.

- For each day I want to see how many of the drivers have had their stops in the category of 100-80%, 80-60% etc.

Hope this clarifies a little.

• ###### 6. Re: Finding the number of occurances in a column

Okay, on my second LOD, try adding date as a dimension:

{FIXED [date]: SUM([Count 80-100])}

I'm using 2018.3, but I'm attaching a copy in 10.5 format.