5 Replies Latest reply on Aug 1, 2018 2:30 PM by Tina Crouse

# LOD Exclude with IFNull

I am trying to get a calculation to work and just not having any luck here. I want to exclude any market where the sum of auths is null and if the sum of auths is not null then calculate the total denied divided by total auths

{Exclude [Market] : IFNULL(sum([Auths])) ELSE

zn(ROUND(sum([Denied]),1)/ROUND(sum([Auths]),1))

end}

• ###### 1. Re: LOD Exclude with IFNull

Hi Tina,

Could you provide a sample workbook for us to look into? The {Exclude:} is a LOD calculation and is not used to "exclude null value".

The calculation may look like

IF ISNULL(Measures) THEN NULL

ELSE Calculation

END

• ###### 2. Re: LOD Exclude with IFNull

Tina,

Try this:

if

ISNULL(sum([Auths]))  then null

else

zn(ROUND(sum([Denied]),1)/ROUND(sum([Auths]),1))

end

• ###### 3. Re: LOD Exclude with IFNull

Hi Tina,

It sounds like you are trying to display your SUM( [Denied] ) measure as a percentage of the total SUM( [Auth]) for each Market, and not show this is the SUM([Auths]) is null. Is this correct? Are you able to upload a dummy workbook? I have made some suggestions below however it an example workbook, screenshot or deeper explanation of your goal may allow us to help you more.

Meanwhile, I would try filtering out any markets where SUM( [Auth] ) is NULL by using the following calculation:

ISNULL( {FIXED [Market] : SUM( [Auths] ) } )

You can then use the following calculation in your view:

ROUND(  SUM( [Denied] ), 1)

/

ROUND( SUM( [Auths] ), 1)

If filtering out those markets at for the entire sheet is not an option, another option may be the following:

IF NOT ISNULL(SUM( [Auths] ))

THEN

ROUND(  SUM( [Denied] ), 1)

/

ROUND( SUM( [Auths] ), 1)

END

Finally if you are looking to change the level of detail here to aggregate across markets, you could try this:

{INCLUDE [Market] :

IF NOT ISNULL(SUM( [Auths] ))

THEN

ROUND(  SUM( [Denied] ), 1)

/

ROUND( SUM( [Auths] ), 1)

END

}

Cheers,

Chris

InterWorks

• ###### 4. Re: LOD Exclude with IFNull

Thank you everyone for the feedback; however, none of these worked. They did not because I have three tabs. The first compares the prior month to the current month. The 2nd shows the difference between the prior month and current month. The third is a 13 rolling month snapshot. The problem here is there are auths within that 13 rolling months for the GA Market for the vendor Orthonet. There are no auths for the prior month or current month. So the dashboard shows the first two tabs correctly as doe the third but the dashboard does not line up because tab one and two say GA does not exist since there are no auths for those 2 months.

• ###### 5. Re: LOD Exclude with IFNull

The problem has been solved. It was because the show hidden rows was not selected under table layout. Selecting that showed the data from the 2 tables that is null because the first table is prior month versus current month values. The 2nd table is the change between the two. The third is a rolling 13 month view. The culprit in this case was filtering on a vendor and the vendor did not have data for GA in May or June. But they did have data for Feb. Now everything lines up when filtering.

1 of 1 people found this helpful