5 Replies Latest reply on May 23, 2014 9:53 AM by Brandon Avants

# Number of Open Cases

I'm trying to recreate the viz below but I don't have the data in this way.

I have created time and due by time.  So, that being said I did the following......

= Days Past Due

DATEDIFF('day',[DueBy Time],TODAY()  )

= On time KPI

IF ([Days Past Due] > 0) THEN 'Late'

else 'On Track'

END

I have come up with this which is wrong by using the above formulas but it's close.

How can I roll up all the open cases through Jan into the January tab, same for Feb if they are still open, etc?

I added a made up workbook Message was edited by: Brandon Avants

• ###### 1. Re: Number of Open Cases

Hi Brandon,

The second screenshot isn't working for me. Nonetheless, can you upload a packaged workbook instead?

1 of 1 people found this helpful
• ###### 2. Re: Number of Open Cases

What Tom said. But in meantime, that second image looks like this, right?

Tableau is doing what you asked it to do in those calculations you created. We need to better understand what you Excel screenshot is using to color the bars.

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 3. Re: Number of Open Cases

This is basically what I get when I throw everything on but not what I need in the final.  In your example it would need ship date.  If it hadn't shipped by the promised ship date it would be "late" and a color and if it was still on target to ship it would be "on time".  Those that haven't shipped in by May 2014 and are all counted in May for on time and late.  Those that are still open with a June date are counted for june.

• ###### 4. Re: Number of Open Cases

In that case you're looking for something like this:

IF DATEDIFF('day',[Order Date],[Ship Date] )>=4

THEN 'Late'

ELSE 'On Track'

END

See attached.

--Shawn

• ###### 5. Re: Number of Open Cases

That works as a date dif yes.  But it doesn't solve the problem of all the cases being added together.  I think I need the closed cases.