13 Replies Latest reply on Jan 13, 2017 9:05 AM by Shinichiro Murakami

# Calculation

Hello,

I'm doing training completion dashboard and currently I only have 4 categories that I put as color: Completed, In progress, Registered, In progress/Past due and Registered/Past due. However, I need to break down Completed into Completed on time and Completed past due which is not in the data source so I need a calculation to have all the categories. How do I create a formula for that? I attempted but it's not right and I included a screenshot of my dashboard and the categories to be used in the calculation:

If [transcript completion date 2]<[transcript due date]
then “Completed on time”

Elseif [transcript completion date 2]>[transcript due date] then “Completed late”

Elseif {transcript completion date2] is Null and [Transcript
Status] is Registered/Past Due or In Progress/Past due then “Past due”

Else “Not due yet”

END

thank you

• ###### 1. Re: Calculation

Hi Liliya

That's way more efficient for both you and us.

Thanks,

Shin

• ###### 2. Re: Calculation

ok, attaching, hope you can see data. It's New hire training completion tab.

• ###### 3. Re: Calculation

Hi Liliya,

You need to tweak your calculation a little

If [transcript completion date 2]<[transcript due date] AND [Transcript Status] = "Completed"
THEN “Completed on time”

ELSEIF [transcript completion date 2]>[transcript due date] AND [Transcript Status] = "Completed" THEN “Completed late”

ELSEIF ISNULL[transcript completion date2]  AND  ( [Transcript Status] = " Registered/Past Due"  OR  [Transcript Status] = "In Progress/Past due" ) THEN “Past due”

Else “Not due yet”

END

Happy Tableauing!

Suhas

• ###### 4. Re: Calculation

HI Liliya

Your book is connected to server and I cannot access.  If Suhas's one works, that's great.

if not, please extract data before saving packaged workbook.

Thanks,

Shin

• ###### 5. Re: Calculation

I extracted the whole book, let me know if it's too big and I will extract a few rows only. The other formula didn't work as I got a message If expected to match...

Thank you

• ###### 6. Re: Calculation

Yes, 18MB is too big.

Could you reduce the size down to around 3MB.

Thank you for the consideration.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: Calculation

ok, extracted less data, sorry.

Thank you

• ###### 8. Re: Calculation

here is even a smaller extract.

Thank you

• ###### 9. Re: Calculation

Liliya

I cannot verify the loic exactly, but made below formula.

[Status calculated]

If [Transcript Completion Date 2]<=[Transcript Due Date]

then "Completed on time"

Elseif [Transcript Completion Date 2]>[Transcript Due Date]

then "Completed late"

Elseif [Transcript Completion Date 2] = Null

and [Transcript Status] = "Registered / Past Due"

or [Transcript Status] = "In Progress / Past Due"

then "Past due"

Else "Not due yet"

END

I'm not sure Elseif [Transcript Completion Date 2] = Null statement  should affect only [Transcript Status] = "Registered / Past Due" or

[Transcript Status] = "In Progress / Past Due" as well.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 10. Re: Calculation

Hi Shin,

Thank you, the formula worked. However, for some reason some records with a Transcript status "completed" have training completion date as Null, how can I incorporate this into a formula statement? And for some reason some records that are marked as Registered/Past Due are in "not due" yet. Any idea why this is the case?

Thank you

• ###### 11. Re: Calculation

Here is the attachment with an example...

• ###### 12. Re: Calculation

Hi Shin,

By trial and error I was able to get the formula right so it works as expected now:

If [Trascript completion date 2]<=[Transcript Due Date] then "Completed on time"

Elseif [Trascript completion date 2]>[Transcript Due Date]

then "Completed late"

Elseif [Transcript Due Date] = Null or

[Transcript Status] = "Completed"

Then "Completed late"

Elseif [Trascript completion date 2] = Null

or [Transcript Status] = "Registered / Past Due"

or [Transcript Status] = "In Progress / Past Due"

then "Past due"

Else "Not due yet"

END

Thank you sooooo much for your help!!!

• ###### 13. Re: Calculation

Sounds good.

Trials and errors now has become your knowledge and heritage.

Thanks,

Shin