5 Replies Latest reply on Sep 19, 2017 6:39 PM by Zhouyi Zhang

# Help with Calcs for Building a Line Chart

Hi, I would like to build a (what I though would be ) simple line graph tracking a running sum of 'outstanding' items from a small sample data set. I have attached a dummy packaged workbook for ease.

The data contains 'open timestamp', 'closed timestamp' (if its been closed) and a 'Global ID'. There can be multiple lines of data for each 'Global ID', so all counting needs to be COUNTD.

i have already created a 'Number of Items' measure and an 'open or closed' Dimension.

What I want is a simple line graph tracking a running sum of how many outstanding items there were on each day, as at that day. I do not know how to do the calcs for this and then track it onto a line graph as a running sum.

I.e. yesterday there was 10 items, 7 were closed, 3 outstanding

today there was 3 items, 2 were closed, 3+1 outstanding.

Line graph to plot 3 then 4 then .... etc

Guy

• ###### 1. Re: Help with Calcs for Building a Line Chart

That should help.

• ###### 2. Re: Help with Calcs for Building a Line Chart

Hi, Guy

Please see my solution attached with 10.3

Below is the result

Hope this could help

ZZ

• ###### 3. Re: Help with Calcs for Building a Line Chart

Hi,

If I understand your business in a right way ... you may need to calculate in a daily bases what is outstanding (Last state is open or none), and what is not outstanding (the last state is close ....

Firstly, modify your data source to allow all the records to be calculated against fixed time table (27 March, 28 March, 29 March and 4 April), include another sheet that contain these time table and cross join to it with your initial data table ... now for each day from this time table you need to identify per each (global ID) the last opening date and the last closing date corresponding to this day, then compare them to decide if the status was closed or opened in that day ...

The below VIZ try apply this on your sample data, the resultant view  display the (global IDs) distributed against the days from the time table and calculate per cell the last opening date, the last closing date and the status and the expected status ....

and here is the VIZ link

Tableau Public

Note:

• ###### 4. Re: Help with Calcs for Building a Line Chart

Hi ZZ,

Thanks for this, are you able to save in version 10.1 though? I only have 10.1 here so cannot access your attachment

• ###### 5. Re: Help with Calcs for Building a Line Chart

Hi, Guy

I only have 10.2 and 10.3 installed, I put the screenshot below to show the steps I did for this. Hope this could help.

ZZ