How to plot a Combination Chart with Data composing of 2 dates (e.g. Created, Closed) on the same Axis

[Thien Ung]

Greetings Spotfire Community and Gurus:

I have the following data set which I am trying to plot on a combination chart with stacked bar charts for the count of tickets Created (positive) and Closed (negative) along with the lines showing Cumulative Created and Net Created (Created - Closed) on the same Axis.

I am having trouble composing the correct SUM OVER expression to plot the cumulative or net created line. Any tips or assistance is greatly appreciated. Thank you in advance.

 

Please find below and attached the Excel and Spotfire.DXP of the data set I am working with.

EXAMPLE OF RAW DATA (Structure)

 

 

 

IDX

SITE

STATUS

CREATED

CLOSED

 

 

CVO4125

EH

OPEN

1/1/2018

 

 

 

CVO4127

VT

CLOSED

2/1/2018

2/20/2018

 

 

CVO4128

LA

OPEN

2/16/2018

 

 

 

CVO4129

VT

CLOSED

4/1/2018

5/1/2018

 

 

 

EXCEL CHART EXAMPLE OF GOAL

 

Knowing that with the information provided in that format I have to UNPIVOT the data correct Hence, here is an example of the UNPIVOT RAW DATA

 

 

 

IDX

SITE

STATUS

ATTRIBUTE

VALUE

 

 

CVO4125

EH

OPEN

CREATED

1/1/2018

 

 

CVO4127

VT

CLOSED

CREATED

2/1/2018

 

 

CVO4127

VT

CLOSED

CLOSED

2/20/2018

 

 

CVO4128

LA

OPEN

CREATED

2/16/2018

 

 

CVO4129

VT

CLOSED

CREATED

4/1/2018

 

 

CVO4129

VT

CLOSED

CLOSED

5/1/2018

 

 

 

 

EXAMPLE OF CURRENT EFFORTS

[Khushboo Rabadia]

You were just missing over condition for [Created] column as well like you have done for [Closed] values

SUM(If([Attribute]="CREATED",1,0)) OVER (AllPrevious([Axis.X])) - SUM(If([Attribute]="CLOSED",1,0)) OVER (AllPrevious([Axis.X])) AS [NET CREATED]

This will bring you correct values for unpivoted raw data graph.

[Thien Ung]

Much appreciated for the affirmation Khushboo

[Thien Ung]

Answer achieved. Please disregard this comment.

[Thien Ung]

Let's say if I had created a Calculated Column [GROUP ATTRIBUTE] that has values of 1 for "Created" and -1 for "Closed".

Then, re-writing the formula to get the [NET CREATED] per year as Sum([GROUP ATTRIBUTE]) OVER Intersect(AllPrevious([Axis.X]), NavigatePeriod([Axis.X], "Year", 0, 0)).

 

Now, how would I ignore the 1st month or node

[Khushboo Rabadia]

It seems FirstNode only works as expected for one level of hierarchy i.e. whichever is the current level. You can try this workaround:

1) Create Mon1 calculated column which will have month values in integer format

Insert > Calculated Column...

Column name: Mon1

Expression: Integer(String(Month([Value])))

2) Then create a column with year month concatenation

Insert > Calculated Column...

Column name: Period

Expression: case when Month([Value]) in (10, 11, 12) then Concatenate(Year([Value]),[Mon1]) else Concatenate(Year([Value]),0,[Mon1]) end3) Then in custom expression, you can rank by this period column and exclude values whose rank is 1 i.e. first node of Axis.X

Count(case when DenseRank([Period],"asc",[Year])>1 then [Attribute] end) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0)))Above expression will exclude the value of first node.

Now, if you want to hide first node, you can use Show/Hide items to show only node without 0 value

Expression - THEN [Y.FinalValue]

 

Rule Type - Not equal to 0

 

Show Items