Stacked bar with negative value
I have a dataset as in the attached analysis file and would like to display number of closed tickets as negative values, based on the "Closed Date", and have the positive values as the sum of all tickets based on the "Created Date".
Attachments
| Attachment | Size |
|---|---|
 stackedbar.dxp | 277.29 KB |
(3) Answers
You can split by priority by adding that to the color axis next to event:
1 Comment
Thanks again Tobias, but doing so will put each Priority into a separate color, i.e. Created or Closed. Would it be possible for the Priority to be on the same color, without having to adjust the color manually?
1 Comment
Thanks again Tobias, but doing so will put each Priority into a separate color, i.e. Created or Closed. Would it be possible for the Priority to be on the same color, without having to adjust the color manually?
Is this what you are looking for?
Here's what I did to create the above from the data in your file:
- I started by unpivoting the data to get one date column instead of two. This can be done most easily from the data panel by selecting the two date columns and clicking unpivot in the right click menu. This transformed the dataset to give me one row when the ticket was created and one row when the ticket was closed.
- To make it easier to understand the table I renamed the category column created by the unpivot operation to "Event" and the value column to "date". I also replaced the value Create Date to just "Created" and the Closed Date to "Closed". The screenshot above shows the resulting table.
- I created the barchart at the top of the screenshot by using my new date column as the x-axis and the following expression as the y-axis: Sum(if([Event]="Created",1,-1)) as [Number of Tickets].
- Finally I renamed the y-axis and the visualization title to something nice and more understandable than the expression.
3 Comments
Thanks Tobias, but the requirement is to have the break-down by Priority column for each days. Can this be achieved?
Yes, you are right. I did filter out the empty dates. Forgot to mention that.
Good catch on the cancellation. My graph shows net change by priority. If you remove priority you will get absolute net change and if you color by Event you will get sum of created tickets pointing up and sum of closed ticket pointing down as in the illiustration below:
Thanks Tobias, this is definitely help, I believe you also had to exclude those empty dates on the graph. And also, if you look closely at the data on 2nd Sep, there should be 2 tickets created and 1 closed. The grouping by Priority cancel 1 of them out. Is there any way to avoid this?
3 Comments
Thanks Tobias, but the requirement is to have the break-down by Priority column for each days. Can this be achieved?
Yes, you are right. I did filter out the empty dates. Forgot to mention that.
Good catch on the cancellation. My graph shows net change by priority. If you remove priority you will get absolute net change and if you color by Event you will get sum of created tickets pointing up and sum of closed ticket pointing down as in the illiustration below:
Thanks Tobias, this is definitely help, I believe you also had to exclude those empty dates on the graph. And also, if you look closely at the data on 2nd Sep, there should be 2 tickets created and 1 closed. The grouping by Priority cancel 1 of them out. Is there any way to avoid this?