How to count rows based on deferrent columns when using barchart

[Youcef Boubellouta]

Hello,

I want to calculate the number of Asked misions and the number of completed missions based on multiple levels of dates (months, days, hours), knowing that :

asked missions = number of missions grouping bythe creation date

completed missions = number of missions grouping by the end mission date

I want to plot a barchart with 2 bars which representasked missions andcompleted missions in the same time.

the problem is when i try to add a barchart in spotfire, I select the creation date time as X axis, so when I do Count([mission number]) in the Y axis I couldn't do another time for the second bar because it will calculate the number of missions based on creation date.

[Fabian Duerr]

You could create a new data table with an unpivot transformation. (Please rename the columns 'category' and 'value' to something meaningful):

 

And then make a bar chart where you filter out empty values in your 'value' column. Use the 'color by' option for data breakdown:

 

Is this what you are looking for

You can also go further with this new data table by plotting the running sum of created and closed missions. Please see the expression for the Y-Axis in the visualization title (I used a line chart here. But you can simply swithc to a bar chart as well):