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

# LOD Not Calculating Correctly at Break Levels

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

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:

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

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

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

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.