5 Replies Latest reply on Jul 5, 2016 11:01 AM by Dario Restrepo

# LOD not behaving as expected - simple ratio Calc.

Hi everyone,

I'm having trouble with a ratio formula that is based on a LOD fixed calculation and uses three different filter options.  I tested the results based on a combination of filter selections and only Scenario 2 has calculated the values correctly.  I believe that the issue may be with the dimensions used in the level of detail.  For me it's important to have all the dimensions represented so that I can drill down to the most granular level of Country, Class, and Affiliate.

I hope someone can help me out.  I'm not sure If the solution is to make different LOD's with different criteria.  I was hoping one LOD calculation would work with any combination of the three filters used.

Thanks in advance.  I'm attaching the Tableau workbook and Excel file used to validate the ratio values.

The Ratio calculation is:        ([VALUES]/[Cartera {LOD}])*12

The LOD calculation that provides the denominator used for the ratios is:

{FIXED [DATE], [Country], [Class], [Affiliate] :

SUM(IF [Variable]="Cartera Total" THEN [VALUES] END) }

Scenario 1:

filter option selected are:

COUNTRY:      (Italy)

CLASS:            (Oro)

AFFILIATE:       (All)

Results:  Tableau is giving me the wrong values for the ratio calculated (see Excel file)

Scenario 2:

filter option selected are:

COUNTRY:      (Italy)

CLASS:            (Oro)

AFFILIATE:       (OFFICE)

Results:  Tableau is giving me the CORRECT values for the ratio calculated (see Excel file)

Scenario 3:

filter option selected are:

COUNTRY:      (Italy)

CLASS:            (ALL)

AFFILIATE:       (OFFICE)

Results:  Tableau is giving me the wrong values for the ratio calculated (see Excel file)

Below is the Excel file used to validate the numbers.  As you see, only Scenario 2 has both Excel and Tableau providing the same result.

• ###### 1. Re: LOD not behaving as expected - simple ratio Calc.

Tow modifications needed.

1. Change LOD formula as below.

[Cartera {LOD} (copy)]

{FIXED [DATE] :

SUM(IF [Variable]="Cartera Total" THEN [VALUES] END) }

2. Put three filters to context filter.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: LOD not behaving as expected - simple ratio Calc.

Shin,

Thank you AGAIN  for coming through for me.  A true ambassador to the forums!

Question,  to help me better understand the solution.  Was the solution to the problem a matter of removing those extra dimensions from the LOD calcuation?  Or was it a combination of updating the LOD calculation AND ALSO adding the Filter to context (ps thank you for introducing me to Filter by context, was not aware of it before)

In other words, If I remove the 'add to context' filter, would I still get the correct results?  After researching quickly a little bit about 'add to context filters', I see that it helps performance by reducing the data set based on the filter selected.  I went ahead and updated your solution worksheet to include filters to make selections and was happy to see that your solution still worked.

Thank you Shin for all your contributions!!

- Dario

• ###### 3. Re: LOD not behaving as expected - simple ratio Calc.

Hi Shin,

I just tested it myself by "Remove From Context " and see that the solution doesn't work.  It must be a combination of both changes you made to the worksheet, (updating the LOD calculation and Also "add to context".

I'm happy the solution works but I really don't understand why it works.  I'll try and read up online to figure out the logic behind it and understand the mechanics.

- Dario

• ###### 4. Re: LOD not behaving as expected - simple ratio Calc.

Dario,

Unfortunately, I could not find a good explanation about the logic behind other than the link below.

I'm sure there were many similar question regarding this issue, but the each question request is different a little bit each time.

You can search past Q&A, or the other good option is you can test many cases and learn by yourself.

Thanks,

Shin

• ###### 5. Re: LOD not behaving as expected - simple ratio Calc.

Thank you Shin,  I will continue to research the matter to get a better understanding but I'm sure the logic will come with time and experience!

All the best to you

Dario