4 Replies Latest reply on Nov 17, 2018 10:31 AM by Don Wise

TIME Series LOD Help

Hello Folks,

I am racking my brain against this LOD that seems pretty straight forward, but it's challenging my thinking of how LODs work.

Question:  How many cards were finished each month?

Problem:  I must be able to use the start date on the 'rows' shelf.   Using the start date completely changes my results....  Naturally.  So I created an LOD... but it doesn't work.

IF ANYONE COULD PLEASE TELL ME WHERE MY LOGIC IS WRONG, it would be greatly appreciated.

• 1. Re: TIME Series LOD Help

Hi Stephen,

I think what you're likely going to have to do is provide an independent set of consecutive dates, using a separate data source such as MS-Excel or other fact table of dates; then, perform what's known as 'Date Scaffolding' using a Cartesian join to capture the correct number of closed cards and the correct number of initiated cards by whatever date value (months) you're binning to.

Instead of using your Start Date, you'll use the new Calendar Date for your bar chart on columns, along with the related calculation from the thread in a filter to count everything between Start and Finish dates, to get the correct numbers.

Please see this thread:  Date Difference by Month for Contract Days or this thread: How do I use 'start date' and 'end date' as examples.

Hope it helps! Thx, Don

1 of 1 people found this helpful
• 2. Re: TIME Series LOD Help

THANK YOU!!!

I appreciate the time you took to reply.  There are several challenges with the data set regarding the available dates.  I will deep dive into your suggestions.

Thanks again!

1 of 1 people found this helpful
• 3. Re: TIME Series LOD Help

Hi Stephen,

Here is my contribution:

MIN(

IF DATETRUNC('month', [Start Date - Actual]) = DATETRUNC('month', [Finish Date - Actual])

THEN { FIXED DATETRUNC('month', [Finish Date - Actual]) : COUNTD([Idea ID]) }

END

)

Please find all the explanation here : https://kb.tableau.com/articles/howto/combining-start-and-end-dates-into-a-single-axis

Cedric

1 of 1 people found this helpful
• 4. Re: TIME Series LOD Help

Hi Stephen,

No worries...glad to have helped ! Don

1 of 1 people found this helpful