4 Replies Latest reply on Jun 21, 2018 7:47 AM by Jeff Passen

    LOD Not Calculating Correctly at Break Levels

    Jeff Passen

      I've set up a calculation to show revenue by certain bands (>$1M, $500K-$1M, $150K-$500K, etc.).  I'm using the 'FIXED' LOD expression as follows:

       

      IF

          {FIXED [Time Rptg Yr Qtr Abbrv], [Geo Thtr Nm], [Ph Vrsn], [Rptg Prod Type Grp], [Trans Maint Type], [Customer Name] : sum([Sale Book Rev Amt])} < .01 THEN '< $0'

              ELSEIF

              {FIXED [Time Rptg Yr Qtr Abbrv], [Geo Thtr Nm], [Ph Vrsn], [Rptg Prod Type Grp], [Trans Maint Type], [Customer Name] : sum([Sale Book Rev Amt])} < 50000 Then '< $50K'

                  ELSEIF

                  {FIXED [Time Rptg Yr Qtr Abbrv], [Geo Thtr Nm], [Ph Vrsn], [Rptg Prod Type Grp], [Trans Maint Type], [Customer Name] : sum([Sale Book Rev Amt])} < 150000 THEN '$50K - $150K'

                      ELSEIF

                      {FIXED [Time Rptg Yr Qtr Abbrv], [Geo Thtr Nm], [Ph Vrsn], [Rptg Prod Type Grp], [Trans Maint Type], [Customer Name] : sum([Sale Book Rev Amt])} < 500000 THEN '$150K - $500K'

                          ELSEIF

                          {FIXED [Time Rptg Yr Qtr Abbrv], [Geo Thtr Nm], [Ph Vrsn], [Rptg Prod Type Grp], [Trans Maint Type], [Customer Name] : sum([Sale Book Rev Amt])} < 1000000 THEN '$500K - $1M'

                              ELSE '> $1M'

      END

       

       

      Each of the dimensions in the "FIXED" section of the calculation are also filters.

       

      The total for each quarter matches what I expect it should be, but each band is off.  I'm also doing a distinct count (countd) on customer name.  The total customers and the total for each band is different than what I expect.  I'm comparing the outcome to a data pull that I did (from the same data source) and exported it to Excel then summed up the values by the above stated bands.

       

      What am I doing incorrectly on the LOD expression that's causing the bands to not total correctly?

       

      Thanks, Jeff

       

       

        • 1. Re: LOD Not Calculating Correctly at Break Levels
          Mavis Liu

          Hi Jeff,

           

          Are you using fixed because you want those particular filters to be unaffected?

           

          If you want the data to be updated accordingly to the filters, you'll need to add those filters to context:

           

          2018-06-21_14h30_21.png

           

           

          If that isn't it, please could you provide us with a sample workbook.

           

          Thanks,

           

          Mavis

          • 2. Re: LOD Not Calculating Correctly at Break Levels
            Jim Dehner

            Good morning

            you say the Bands are off - how so?

             

            I did note that all your measures are <   what happens then the If test results in an Equal - the mark will go to the higher sales band - is that what is throwing you off?

             

            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: LOD Not Calculating Correctly at Break Levels
              Jeff Passen

              Mavis, I am using Context.

               

              Jim, I did include ‘<=’ in the original calculation and changed it to what it currently is to see if there was an issue.  The numbers are exactly the same with both calculations.

               

              Unfortunately I cannot provide a sample workbook because it’s confidential data.

               

              The desired outcome is to see the data in each of the total # of customers for each revenue bands and how much revenue is in each band

              for each quarter and by product group (there are two product groups).

               

              The following is a sample of the Excel extract.  I’ve fictionalized the data by multiplying a percentage factor to each value. The 2nd chart below is the extract from Tableau (using the same percentage factor) to show how the data is different between the two.

               

              You’ll notice a ‘De-Book’ line in the Excel chart – this represents returns or $ value <0.  The Excel version shows the correct number.  The Tableau version (< $0) does not, but both total to the exact amount.

               

               

               

              2017-Q1

              2017-Q2

              2017-Q3

              2017-Q4

              Total Revenue

              $98,111,786

              $90,866,028

              $91,473,847

              $118,079,326

              Total Customers

              1041

              1091

              1058

              1244

              # > $1M

              17

              17

              17

              23

              # $500K - $1M

              29

              20

              19

              38

              # $150K - $500K

              84

              93

              89

              88

              # $50K - $150K

              111

              129

              134

              154

              # < $50K

              800

              832

              799

              941

              # Total Check

              1041

              1091

              1058

              1244

              $ => $1M

              $35,776,346

              $31,433,240

              $31,009,331

              $43,228,593

              $ =>$500K < $1M

              $19,253,807

              $13,582,286

              $13,624,836

              $27,222,870

              $ =>$150K < $500K

              $24,044,130

              $25,353,080

              $25,143,797

              $24,089,870

              $ =>$50K < $150K

              $9,794,337

              $10,995,964

              $11,858,184

              $13,059,209

              $ < $50K

              $9,909,898

              $10,056,266

              $10,300,178

              $12,706,453

              Total De-Books

              $666,732

              $554,808

              $462,477

              $2,227,670

               

               

               

              2017-Q1

              2017-Q2

              2017-Q3

              2017-Q4

               

              BOOK

              # Customers

              BOOK

              # Customers

              BOOK

              # Customers

              BOOK

              # Customers

              Grand Total

              $98,111,786

              1,107

              $90,866,028

              1,216

              $91,473,847

              1,117

              $118,079,326

              1,286

              > $1M

              $29,220,365

              14

              $26,920,425

              15

              $21,743,104

              11

              $35,735,736

              19

              $500K - $1M

              $20,299,127

              30

              $9,685,896

              14

              $15,289,797

              20

              $24,484,036

              34

              $150K - $500K

              $24,985,371

              89

              $28,172,456

              100

              $27,175,077

              96

              $28,189,917

              104

              $50K - $150K

              $11,150,442

              130

              $12,465,643

              139

              $13,788,456

              155

              $15,016,871

              177

              < $50K

              $13,310,378

              978

              $14,312,297

              1,025

              $14,055,317

              1,003

              $16,953,705

              1,167

              < $0

              ($853,898)

              95

              ($690,690)

              151

              ($577,903)

              62

              ($2,300,939)

              56

              • 4. Re: LOD Not Calculating Correctly at Break Levels
                Jeff Passen

                I was able to figure out how to solve this problem.

                 

                1) Create a Boolean filter for each Revenue Band (ex: {FIXED [Time Rptg Yr Qtr Abbrv], [Ph Vrsn],  [Customer Name] : sum([Sale Book Rev Amt])}  > 1000000) for the > $1M Band

                2) Create a calculated field (called Revenue Band) that states If [Insert Boolean Filter Name Here] = TRUE then 'INSERT REVENUE BAND HERE' ELSEIF (next Boolean filter).

                3) Put Revenue Band on the Rows Shelf

                4) Voila

                 

                Thanks to those that responded to my initial question.