5 Replies Latest reply on Jun 14, 2013 8:51 AM by Joshua Milligan

Count above and below a variable value

I am having a challenge with what seems like should be a relatively simple task.  I have a number of locations in my data set.   I want to create a dashboard that allows an individual to select "My Location" and then explore the world from their perspective.

An example of this approach is to get a count of the number of locations with higher sales than "my location" and the number with lower sales. (I'm actually interested in the % with higher and % with lower but the first step is getting a count).

I have attached an example with fake data to illustrate the problem in it's simplest form.   In it there are twenty locations, each with a sales figure and each tagged, by color, as to whether it has higher or lower sales than "My Location" (which is controlled via parameter).

I can see the number of locations in each category but want to get a count and/or % with higher and lower sales.  Is there a way to do this?  I'm hoping there is a simple Table Calc of which I am unaware?

Thanks,

- Chris

• 1. Re: Count above and below a variable value

Chris,

Your table calc was the first step.  I created a few more calculations that wrapped it in a WINDOW_SUM.  You can see a possible solution in the attached workbook.

Regards,

Joshua

• 2. Re: Count above and below a variable value

Joshua,

Thanks for your proposed solution. It works well.   I suspected there was a way with the table calcs but kept running into a wall.  You saved me many more frustrating hours.

One follow-up question.  Is there a way to do this without actually having the table, Just the "above and below" figures you put in the title?  I was just playing with your workbook and thought that perhaps if I moved Location from the Rows to the Level of Detail it would still calculate correctly but no luck.  Any thoughts/suggestions?

Thanks again for your help.  I truly appreciate it.

- Chris

• 3. Re: Re: Count above and below a variable value

Chris,

Sure!  Here is an updated workbook that shows how this could be done.  Basically, I moved the title to the caption and then used a table calc filter to show only the row for "My Location".  I hid a few headers and then I end up with a view where I can  see My Sales and the Higher and Lower counts/percentages.  A dashboard makes it all fit together.

Regards,

Joshua

• 4. Re: Count above and below a variable value

Fantastic.  Thanks.

Chris Moseley

415.577.4144