1 Reply Latest reply on Mar 21, 2018 11:26 AM by Michel Caissie

    Long time to load View with multiple LOD

    Eleonor Hellblom

      Hi,

       

      I am working with an Impala database, meaning I am now unable to use the COUNTD function.

      My workaround on this is using an LOD calculation (This example for YTD 17):

       

           {INCLUDE [ID] : COUNT(IF [Desks Grouping] = 'EMEA' AND YEAR([Date]) == 2017 AND (MONTH([Date]) < MONTH([Last Data Refresh]) OR      (MONTH([Date]) == MONTH([Last Data Refresh]) AND DAY([Date]) <= DAY([Last Data Refresh]))) THEN ([ID]) END)}

           /

           {INCLUDE [ID] : COUNT(IF [Desks Grouping] = 'EMEA' AND YEAR([Date]) == 2017 AND (MONTH([Date]) < MONTH([Last Data Refresh]) OR      (MONTH([Date]) == MONTH([Last Data Refresh]) AND DAY([Date]) <= DAY([Last Data Refresh]))) THEN ([ID]) END)}

       

      As I need a table of the different desks and total I have these Dimensions x 3 (YTD 18, YTD 17, YoY):

      Total All

      America

      Total EMEA/APAC/ LATAM

      EMEA

      APAC

      LATAM

       

      So in total 18 different calculated fields containing LODs, mostly multiple and 'Last Data Refresh' is an LOD as well.

       

       

      When I put these on a table it takes over 350 minutes to load (still counting):

       

      I have attached a dummy workbook, just to show the layout of the data and what kind of calculations I am using. Only a small one so doesn't have the issue of long load time - my original data has +500k rows. I am also breaking the table out by 3 dimensions, but that is just a normal dimension - no calculated field or anything.

       

      This is clearly not a sustainable solution, so does anyone know a better way to do this/LOD-CountD calculations? Or a better way to over come the Count Distinct issue and do YTD calculations?

       

      --   Perhaps creating an extract would be the final solution if that would speed it up, but we have limited space on the server so that would be the last outcome.

       

       

      Thank you all in advance!

        • 1. Re: Long time to load View with multiple LOD
          Michel Caissie

          For a starter you can compute the  COUNTD(ID)   with SUM(ID (for cntd))

          where ID(for cntd)  is {FIXED [ID]: MIN(1)}

           

          Depending on the final view you are looking for, this would give you the opportunity to

          put the  Desks Grouping in the view and make calculations only based on the dates.

          Maybe even a single calculation that you could put on the filter shelf something like;

           

          case dateParam

          when '17' then if  date < whatever  then 'In'

          when '18' then if  date < whatever  then 'In'

          else 'Out' end

           

          and you could use the Tableau Totals.

           

          Obviously, if you have different date logic for each Desks Grouping  you wont be able to use a global filter and will have to filter directly in the calculations, but at least by using the ID(for cntd) you will get rid of a bunch of Includes .

           

          Michel