3 Replies Latest reply on Nov 15, 2016 3:41 PM by Rekha Kesavan

    Trouble Using a Fixed LOD Calculation with a Case (or IF) Statement

    David Hernandez

      Hello,

       

      I am attempting to combine a fixed LOD calculation with a Case statement and am having difficulty getting the results I want. Basically, I have a data set that is at varying levels of granularity (therefore a ton of duplication) and I want to fix a date calculation at the ID and Category level. I have a sample of the data structure below and in a workbook attached.

       

      IDCategorySub-CategoryCreate DateComplete DateSnapshot Date
      1A1A1/1/20151/5/201511/1/2015
      1A1A1/1/20151/5/201511/2/2015
      1A1A1/1/20151/5/201511/3/2015
      1A1A1/1/20151/5/201511/4/2015
      1A1A1/1/20151/5/201511/5/2015
      1A2A1/1/20151/5/201511/1/2015
      1A2A1/1/20151/5/201511/2/2015
      1A2A1/1/20151/5/201511/3/2015
      1A2A1/1/20151/5/201511/4/2015
      1A2A1/1/20151/5/201511/5/2015
      1B1B9/26/20159/29/201511/1/2015
      1B1B9/26/20159/29/201511/2/2015
      1B1B9/26/20159/29/201511/3/2015
      1B1B9/26/20159/29/201511/4/2015
      1B1B9/26/20159/29/201511/5/2015

       

      This date calculation takes the difference between the Create and Complete date (DATEDIFF) and forces the ID and Category dimensions to be at the most granular value.

       

      {FIXED ID, Category: MIN(DATEDIFF('day',[Create Date],[Complete Date]))}

       

      For this example, for ID 1 and Category A, the value is 4 days. I am attempting to use a Case Statement where I receive the Fixed LOD calc value if the Category is A and setting the value to 0 for all other categories.

       

      If Category = "A"

      Then {FIXED ID, Category: MIN(DATEDIFF('day',[Create Date],[Complete Date]))}

      Else 0

      End

       

      For ID 1 and Category A, the value is 20 days. For ID 1 and Category B, the value is 0 days. The case statement is working correctly, however, I am expecting the output to be fixed to 4 days, not 20 days.

       

      Any ideas as to why this is occurring? My example workbook is attached. Thanks in advance for any help!