10 Replies Latest reply on Apr 27, 2018 8:37 AM by Yuriy Fal

Calculated field showing extra line of data

Hi there,

Wasn't sure how to explain this in the title!

I have attached an example workbook with two tabs.

The FULL DATA and RESULT tabs, along with the calculation, are shown below:-

So if the TASK is equal to A, the RESULT should read as follows with one record row per acc no:-

ACC NO.....CALCULATION 1

123.............03-JAN-18

456.............NA

At present, the RESULT gives me an extra NULL row for acc 123 and a value of NULL for acc 456.  Acc 456 should read NA.

• 1. Re: Calculated field showing extra line of data

Hi Paolo,

Please write a case statement and handle all the values of task including null values.In calculation1 you are handling only when it is A.use case statement in your calculation.

I don't have tableau installed in my desktop to check your workbook.

BR,

bharat

• 2. Re: Calculated field showing extra line of data

Hi Paolo,

First, your calculation is a Row-Level one,

so the result is calculated for every row in your data --

including the rows with the values of

...

[Acc No] = 123, [Task] = 'B'

[Acc No] = 123, [Task] = 'C'

...

etc.

Second, the result of the [Calculation1] is of the Date Type.

And the DATE( 'NA' ) would give a Null value for every row

where [Task] != 'A' OR [End Date] is Null.

Third, the [Calculation1] is a Dimension, so when on a view (on Rows)

it creates Headers for every unique value (including Null).

That's what you're seeing on the Result view.

To get the result you want -- one row for every Acc No

with either a valid End Date (string) value or 'NA' --

you may want to write your [Calculation1] like this:

IFNULL( STR( {FIXED [Acc No] : MAX( IF [Task] = 'A' THEN DATE ( [End Date] ) END  ) } ), 'NA' )

Hope it could help.

Yours,

Yuri

1 of 1 people found this helpful
• 3. Re: Calculated field showing extra line of data

Many thanks Kumar and Yuri for your replies.

I have used Yuri's suggestion as it fits better with the data I currently have.

Have a great day!

Best regards.

• 4. Re: Calculated field showing extra line of data

Use this calculation

iif(isnull([EndDate]),'NA',[EndDate])

,null)

and filter the calculation to exclude NULLs on the view

• 5. Re: Calculated field showing extra line of data

HI Yuri,

Just one more question please.  How can I convert the result into the following format:-

03-JAN-2018

I am wrapping your calculated field with several different date concatenations but keep receiving errors.

If I convert the data type to date, then I lose the 'NA' value as it converts back to null.

Many thanks again!

• 6. Re: Calculated field showing extra line of data

Paolo,

What data type is your [End Date] column?

• 7. Re: Calculated field showing extra line of data

The [End Date] data type is DATE and set to DD-MMM-YY, which is the preferred format.

I have managed to create this calculated field which works, but I do not get 03 for day, just get 3:-

IFNULL( STR( {FIXED [Acc No] : MAX( IF [Task] = 'A' THEN

DATENAME('day', ( [End Date] ))+'-'+

LEFT(DATENAME('month', ( [End Date] )),3)+'-'+

DATENAME('year', ( [End Date] ))

END  ) } ), 'NA' )

Thanks!

• 8. Re: Calculated field showing extra line of data

it could be

IFNULL( {FIXED [Acc No] : MAX( IF [Task] = 'A' THEN

RIGHT( STR(0) + DATENAME('day', ( [End Date] )), 2) +'-'+

LEFT(DATENAME('month', ( [End Date] )),3)+'-'+

DATENAME('year', ( [End Date] ))

END  ) } , 'NA' )

1 of 1 people found this helpful
• 9. Re: Calculated field showing extra line of data

Many thanks for all your help.  Have a great weekend!

• 10. Re: Calculated field showing extra line of data

Paolo, you're welcome.