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

    LOD Exclude with IFNull

    Tina Crouse

      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
          Heqing Huang

          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
            Ankit Bansal

            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
              Chris Hastie

              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
                Tina Crouse

                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
                  Tina Crouse

                  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