11 Replies Latest reply on May 17, 2018 11:16 AM by zack.douglas

# Calculated Number of Days passed with Pivoted Fields?

Hello,

In my data source I had four dates that I turned into pivoted fields to create a status tracker in tableau. One of the fields were called 'Open Date'. I am trying to calculate the number of days passed since the 'Open Date' but I need help creating the calculated field.

I am using the formula below but it won't single out the "Open Date' portion of the pivoted field. Any ideas?

ATTR(DATEDIFF('day',[Pivot Field Names]='Open Date',TODAY(),'sunday'))

Thanks

• ###### 1. Re: Calculated Number of Days passed with Pivoted Fields?

Hi Zack

datediff would be looking for a date so try thsi

if [Pivot Field Names]='Open Date' then

ATTR(DATEDIFF('day',[pivot value],TODAY(),'sunday')) end

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculated Number of Days passed with Pivoted Fields?

Thanks for replying Jim,

I entered your formula and received the following two errors :

1) reference to undefined field [pivot value]

2) cannot mix aggregate and non-aggregate comparisons or results in "if" expressions • ###### 3. Re: Calculated Number of Days passed with Pivoted Fields?

Try these:

if [Pivot Field Names]='Open Date' then

(DATEDIFF('day',ATTR([pivot value]),ATTR(TODAY()),'sunday')) end

May be this:

if [Pivot Field Names]='Open Date' then

(DATEDIFF('day',ATTR([pivot value]),(TODAY()),'sunday')) end

First one should work

• ###### 4. Re: Calculated Number of Days passed with Pivoted Fields?

Hi Deepak,

Still no luck using both calculated fields you provided. I get the following two errors:

1) Reference to undefined field [pivot value]

2) Datediff is being called with (string,integer,date,string), did you mean (string,datetime,datetime.string)?

• ###### 5. Re: Calculated Number of Days passed with Pivoted Fields?

Can you please attach a workbook, unfortunately I am not good at Guessing

• ###### 6. Re: Calculated Number of Days passed with Pivoted Fields?

Zack when you pivoted the data there were 2 columns that resulted - Pivot field Names and Pivot field Values - you may have changed the name of Pthe pivot filed vlues will contain dates - if you change the name to something else you will have to use that

Jim

• ###### 7. Re: Calculated Number of Days passed with Pivoted Fields?

Hi Deepak,

Please find my workbook attached. For the first error in the calculated field you sent

1) Reference to undefined field [pivot value] ---- this is because the field should be named [pivot field value]

Please take a chance to look at the workbook and let me know if you can calculate the number of days passed. One thing I'd like to note is that some projects have been opened and closed multiple times so if you could help identify days passed from the most recent open date that would be great.

Thanks!

Zack

• ###### 8. Re: Calculated Number of Days passed with Pivoted Fields?

Please see the attached

is this what you wanted Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 9. Re: Calculated Number of Days passed with Pivoted Fields?

Hi Jim,

This is great the numbers are matching up for me! Do you know how to display 'date diff' from the most recent open date?

• ###### 10. Re: Calculated Number of Days passed with Pivoted Fields?

see the attached

the max open date is - and date diff becomes and the viz is this Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 11. Re: Calculated Number of Days passed with Pivoted Fields?

Thank you for all your help Jim. You've provided me the solution to my question.

Best,

Zack