3 Replies Latest reply on Sep 12, 2018 2:57 PM by Okechukwu Ossai

# Table % difference calculation does not work when there are no values in the first month's fields - Why?/One filter instead of two?/Data doesn't change no matter the filter selection?

Workbook Version 10.5 attached

Hello,

I have three main problems:

1) RED I have a workbook that on the Summary by Examiner page (second dashboard), I find some % calculations do not display when the first month of a two month comparison has no values.  I looked at the calculation and it's using zn for the lookup (ex: (ZN(COUNTD([Claim Number])) - LOOKUP(ZN(COUNTD([Claim Number])), -1)) / ABS(LOOKUP(ZN(COUNTD([Claim Number])), -1)) from Overall New Claims % by Examiner sheet.  How do I fix this calculation

2) PURPLE I inherited the workbook design from someone else and I have to use two different filters to display all the stats but the examiner's Closing Ratio.  Is there a way to only use the top right corner filter for everything?

3) BLUE Only one examiner has a litigated claim but that area displays the same (1 claim) no matter which examiner filter selection is chosen.

Any and all help would be most appreciated.  Thank you.

~S

• ###### 1. Re: Table % difference calculation does not work when there are no values in the first month's fields - Why?/One filter instead of two?/Data doesn't change no matter the filter selection?

Hi Conacher,

1) RED The formula says (Current row - Previous row) / Previous row. This means if first month (previous month) of a two month comparison is null, then the formula will return null because you are dividing by null. Even if you convert it to zero using ZN(), division by zero will still return an error or blank. This is not something you can fix, except you want to change your calculation logic or ensure the first month will always have values, otherwise there will be nothing to compare.

2) PURPLE Examiner's Closing Ratio is from a different datasource. Only fields from the primary datasources can be used on the page shelf. One option you have is to use parameters instead. A parameter can be shared by all datasources in the same workbook.

3) BLUE The solution above will resolve this issue also.

Most of the sheets on the dashboard are floated. Try putting them in containers and fixing their widths or heights. Consistent decimal places for all values is ideal also. All these will help keep the sheets in place with each examiner selection.

Create parameter [Examiner Selection]. Add more examiners to the list of values if required. I added the parameter to the page shelf but it will still work if you don't add it.

Create calculated field [Selected Examiner]

[Examiner] = [Examiner Selection]

Add this as a filter to all relevant sheets and set to True.

On the dashboard, add [Examiner Selection] parameter and remove previous examiner page control and filter.

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 2. Re: Table % difference calculation does not work when there are no values in the first month's fields - Why?/One filter instead of two?/Data doesn't change no matter the filter selection?

Sorry to be so late in responding.  Even with a severe head cold you're wonderful instructions were easy to follow.  I have implemented the Examiner Selection parameter filtering.

Thank you soooooooo much.

• ###### 3. Re: Table % difference calculation does not work when there are no values in the first month's fields - Why?/One filter instead of two?/Data doesn't change no matter the filter selection?

You are welcome. I'm glad it helped.