2 Replies Latest reply on Aug 19, 2015 2:19 PM by Mengzhou Xie

    Level of Detail Expressions (LOD) is much slower than Window Calculation?

    Mengzhou Xie
    Mengzhou Xie Aug 19, 2015 12:38 PM

      Hi,

       

      Let me know if I missed some other posts regarding this...

      LOD is a cool feature in Tableau 9.0 and I really want to incorporate it into my visualizations that were built in 8.x. However, when I tried to replace window calculation with LOD, I found LOD implementation is much slower than window calculation implementation.

       

      So here is what I want to achieve:

      I want to get the average of the top n monthly sales of each manager from these three columns: Manager, Month Index and Sales (easy enough). However, I only want to display one record per manager.

       

      Here's my original solution in Tableau 8.x:

      1. Get the rank of sales for each manager

      2. Average only if rank is less than or equal to n

      3. Use fake filter trick to hide additional rows --- index() OR lookup(min(...),0)

      The biggest drawback for this trick is that fake filter cannot be applied on multiple worksheets, so you will stuck when you want to have a global control of two or more worksheets (parameter seems to be your only choice, let me know if I am wrong).

       

      When I learnt LOD, I felt there might be a better and cleaner solution for this since true filter won't affect the result of LOD:

      I followed example 10 (http://www.tableau.com/LOD-expressions) and created a nested LOD to get the top 7. Although this "7" makes the whole thing no longer dynamic, I am still happy. The calculation speed is something I concern the most. To get top 7 according to example 10, I need to create 7 calculated field and in turn, have a 6-layer nested LOD. This increases the time elapsed from 1s to 1min... which is not acceptable.

       

      I cannot think of other solutions using LOD. So please help if you have brilliant ideas to utilize LOD to solve this problem.

       

      Enclosed is a mockup of what I tried to do. You can change the "Force Recalculation" to force a refresh. Even it is not that much, you will see that the LOD implementation takes more time.

        • 1. Re: Level of Detail Expressions (LOD) is much slower than Window Calculation?
          Ben Page
          Ben Page Aug 19, 2015 1:58 PM (in response to Mengzhou Xie)

          Hello,

           

          Yea I'm not sure LOD is your best bet here. It makes a lot of situations much easier, but you shouldn't be creating 7 calculated fields. You are correct in that a parameter will offer global control for your sheet. Instead of your Index() filter, you should try:

           

          [Sales Rank] <= [Avg of Top N parameter]. Also, if you put Sales Rank on the row shelf and hide the header, you'll be able to see the sum of sales for those top N sales.

           

          Ben

          1 of 1 people found this helpful
          • 2. Re: Level of Detail Expressions (LOD) is much slower than Window Calculation?
            Mengzhou Xie
            Mengzhou Xie Aug 19, 2015 2:19 PM (in response to Ben Page)

            Thanks, Ben.

            Just want to make sure if there is a way that allows me to get rid of this fake filter trick. This does make things much more complicated, esp. all other calculations are based on this high average sales number. I had to drop manager and month index to almost every worksheet that I built and hide those unnecessary rows. Do you have alternatives to resolve this issue?


            (The reason why I used index() over [Sales Rank] <= [Avg of Top N parameter] is that I do not necessarily want to show top n sales but just the high average sales for each manager (for example, this high average sales number will determine year end bonus for those managers).)