Jump to content
We've recently updated our Privacy Statement, available here. ×

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


Thien Ung

Recommended Posts

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

Link to comment
Share on other sites

  • 2 weeks later...
  • 5 months later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...