Combination Chart Two Data Types (Integer and TimeSpan)

[April Steele]

I need to do a combo chart where I have volumes on one side of the Y Axes (integer) and Turn Around Time (TimeSpan-in hours and minutes) on the other Y Axes (see attached example in excel). I keep getting an error because they are not the same data types. Is there a workaround for this?

[David Boot-Olazabal]

Hi April,

Could you share a sample dataset or a sample dxp file? That would make it easier to have a look at it.

Kind regards,

David
 

[Gaia Paolini]

A simple workaround could be to turn your time span into an integer (by surrounding it with the Integer(..) function.

[April Steele]

This is a segment of the data set used (original is the columns in green). I would like to do something to the combo chart.

Data Set for Combo Chart.xlsx

[April Steele]

@Gaia Paolini I did that and it worked but I want the display to be in hours and minutes. When I do the it as an integer it makes for example 2 hr 56 minutes (row 8 in the new attached) as an integer in minutes it would be 176.63 minutes. I need to show it by hours minutes because I don't want them to have to convert it to hours minutes which is how they are use to seeing the times. 

 

@David Boot-Olazabal I updated the data set to show exactly what I am needing. The format on the prior wasn't correct.

Data Set for Combo Chart Updated.xlsx

[David Boot-Olazabal]

Hi April,

Thank you for the provided excel sheet. That makes a easier to see what Spotfire should be doing.
I don't think you will be able to add this 'time' variable with the number of cases. I have tried to mimic your pivot table in Spotfire, but the average TAT isn't possible in Spotfire (the average isn't showing for the TAT column when pivoting).
And as the combination of time and categorical variables are not a match for the y-axis, you may have to display the data a bit differently. For instance, setting TAT on the x-axis and color the bars by month would give you this:

Other visualizations may do as well, but that probably defies the initial idea.

What you always can do, as your preferred option isn't possible, is to file an enhancement request via https://ideas.spotfire.com. There is already one that looks what you need, so you may want to vote that one up: https://ideas.spotfire.com/ideas/TS-I-8525.

Kind regards,

David

[Bruno]

Hi,
In my opinion, it is possible with some creativity. I had the same issues/requirements in the past. My solution was to convert the timespan to an ‘integer’ that resembles the timespan, and after that, I could use it. By using the formatting options, it looks like a timespan.


maybe it helps
regards
Bruno

[David Boot-Olazabal]

Hi Bruno,

That looks great!

Can you share your custom expressions or the dxp file?

Kind regards,

David

[Bruno]

sure,

Data Table.dxp

[David Boot-Olazabal]

Thanks Bruno,

I had to add a final custom expression to get rid of the AM postfix in the integer of column3. As my colleague Gaia did see the line immediately, this could be due to my Spotfire version (14.4 HF3). But once that was done, I can see the line in the combo chart!

Thank you for sharing your solution! Very helpful.

Kind regards,

David

[Olivier Keugue Tadaa]

Thanks @Bruno

This custom expression should be fine as well  ::::  Avg(Integer(Substitute(Substitute(String(time([TAT])),":",""),".",""))).    could use sum, max, min, etc .... depending on the expected result.

Edited August 29 by Olivier Keugue Tadaa

[Bruno]

But one more small question, in my opinion, aggregation should come before the time([tat]), because an AVG over an integer is different from over a timespan, right?

[Olivier Keugue Tadaa]

Good point.

this one should be fine::::: Integer(Substitute(Substitute(String(time(Avg([TAT]))),":",""),".",""))

 

Edited September 2 by Olivier Keugue Tadaa