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

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.

 ID Category Sub-Category Create Date Complete Date Snapshot Date 1 A 1A 1/1/2015 1/5/2015 11/1/2015 1 A 1A 1/1/2015 1/5/2015 11/2/2015 1 A 1A 1/1/2015 1/5/2015 11/3/2015 1 A 1A 1/1/2015 1/5/2015 11/4/2015 1 A 1A 1/1/2015 1/5/2015 11/5/2015 1 A 2A 1/1/2015 1/5/2015 11/1/2015 1 A 2A 1/1/2015 1/5/2015 11/2/2015 1 A 2A 1/1/2015 1/5/2015 11/3/2015 1 A 2A 1/1/2015 1/5/2015 11/4/2015 1 A 2A 1/1/2015 1/5/2015 11/5/2015 1 B 1B 9/26/2015 9/29/2015 11/1/2015 1 B 1B 9/26/2015 9/29/2015 11/2/2015 1 B 1B 9/26/2015 9/29/2015 11/3/2015 1 B 1B 9/26/2015 9/29/2015 11/4/2015 1 B 1B 9/26/2015 9/29/2015 11/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!

• ###### 1. Re: Trouble Using a Fixed LOD Calculation with a Case (or IF) Statement

In the attached example you are applying a SUM aggregation to the output of the case statement.

The case statement is going to evaluate to '4' multiple times. Thus, your aggregation should probably be set to MIN.

1 of 1 people found this helpful
• ###### 2. Re: Trouble Using a Fixed LOD Calculation with a Case (or IF) Statement

Hi David,

The aggregation for the Case Calculation should be set to "Min"

1 of 1 people found this helpful
• ###### 3. Re: Trouble Using a Fixed LOD Calculation with a Case (or IF) Statement

Hi Rekha Kesavan and Tom W, thanks for your response, but let's say I wanted to aggregate this calculation up to the ID level - I wouldn't be able to because the value for ID 1 would take the value of 0 due to Category B.

I actually found a solution to this (I'm still vetting it), but it appears if I perform a Fixed LOD on the case statement, I will get the appropriate result. Still trying to understand the underlying logic behind this.

{FIXED

[Category], [ID]:

MIN(If Category = "A" Then {FIXED ID, Category: MIN(DATEDIFF('day',[Create Date],[Complete Date]))} Else 0)

End

}