8 Replies Latest reply on Jan 17, 2017 8:06 AM by mursid rahman

# Calculated Field Doesn't Show Its Value

Hello,

I'm having trouble with calculated field. Basically, I've created 4 calculated fields for my analysis but one of the calculated field doesn't show its value. I really need the help.

List of calculated fields:

1. Avg. VPP (working)

SUM(number of records) / COUNTD(patient code)

2. H1 VPP (working)

IF attr(service date month group) == 'H1' THEN Avg. VPP END

3. H2 VPP (working)

IF attr(service date month group) == 'H2' THEN Avg. VPP END

4. Inflation VPP (not working)

(H2 VPP - H1 VPP) / H1 VPP

I've tried using sum for calculated field number 4 (Inflation VPP ) but it turned out to be invalid.

(SUM(H2 VPP) - SUM(H1 VPP)) / SUM(H1VPP) Am I missing something? Or is there any other simpler method for the intended result that I wanted?

Thank you.

• ###### 1. Re: Calculated Field Doesn't Show Its Value

HI Mursid,

Can you share your workbook?

• ###### 2. Re: Calculated Field Doesn't Show Its Value

Hello Norbert!

Thank you for reaching back. Here is my sample workbook. Thanks a lot!

• ###### 3. Re: Calculated Field Doesn't Show Its Value

Hi Mursid,

You have NULL values in H2 VPP and H2 VPP. That's why the calculation shows no value in the last calculation.

Solution is to use the IFNULL function and tell Tableau what to use instead of NULL.

Greetings,

Matthijs

• ###### 4. Re: Calculated Field Doesn't Show Its Value

Hi Matthijs,

Thank you for the solution. The values are showing but I think I've missed something and the values are now showing 0. Here is what I did for the last calculation;

Inflation VPP

IFNULL(([Avg H2 VPP] - [Avg H1 VPP]) / [Avg H1 VPP], 0 ) • ###### 5. Re: Calculated Field Doesn't Show Its Value

Hi Mursid,

You used the ISNULL as a wrap around the whole formula.

It is better to use the formula for each independent measure. Then you're letting Tableau know what to do for each NULL it encounters in the formula.

2 of 2 people found this helpful
• ###### 6. Re: Calculated Field Doesn't Show Its Value

Hi Matthijs,

This is what I've done. Based on the result I think I'm doing it wrong again.

Calculated fields created:

1. Avg VPP

- IFNULL(SUM([Number of Records]) / COUNTD([Patient Code]), 0)

2. Avg H1 VPP

- IF ATTR([Service Date (Months) (group)]) == 'H1'

THEN [Avg. VPP]

ELSE 0

END

3. Avg H2 VPP

- IF ATTR([Service Date (Months) (group)]) == 'H2'

THEN [Avg. VPP]

ELSE 0

END

4. H1 VPP & H2 VPP

- IFNULL([Avg H1 VPP],0)

- IFNULL([Avg H2 VPP],0)

5. Inflation VPP

([H2 VPP] - [H1 VPP ]) / [H1 VPP ] • ###### 7. Re: Calculated Field Doesn't Show Its Value

Could you tell me what the results should be for the last measure? For instance the first two observations?

• ###### 8. Re: Calculated Field Doesn't Show Its Value

Yes, of course. But first of all thank you for your time, sir.

Malignant of neoplasm of specified parts petroneum:

(10.0 - 10.0) / 10.0 = 0

Malignant neoplasm of breast of unspecified site:

3 (8.17 - 8.05) / 8.05 = 0.015