Also, I am using the same field (instance ID) as a count for my measure. I don't know if that makes a difference.
Two dates on the same axis? Wouldn't you rather put one on a dual axis. That way you could keep your measure on one graph, but be represented by two different dates.
Well I'm trying to do an area chart with the number opened and number closed for each month.
Sorry I'm not sure how you could accomplish that with an area chart. Without using a different type of visualization, the only way I can think of to add two dates is by running a synchronized dual axis with your instance ID setup as discrete. That would show you a mark (like a circle) on the date an item opened and another on the date it closed.
I am having the same problem. I am trying to calculate a cancellation rate, and the equation is as follows:
[Total Amount of Cancellations in a particular month] / [Total Amount of Purchases in the same month]
My data has both a Cancel Date and a Purchase Date... and I need to overlay both on the same axis essentially. Any help from others?
Yes Tracy! I didn't know about the dual axis function....
A follow-up question though: Say in your graph, I wanted to calculate the total $ of items shipped / total $ of items ordered within each month... is there anyway to do that and present that as one singular line graph?
Sure, you would just create a calculated field similar to sum(Sales)/total(sum(Sales)) and place it on the rows shelf.
I tried that but unfortunately, I think the Total() only works across rows so it is summing all the month's sales sums for each particular metric. So all of my month's %'s equal 100% for shipped.. and then 100% for ordered.
However, I was hoping to calculate within each month the %... so the total $ of items shipped / total $ of items ordered within each month.
I've added screenshots of what I have. I'm ending up with 2 lines that represent the % of total of each metric by month. What I'm expecting is just one line that calculates one % based off both metrics for each month.
I recently achieved something similar by adding a new data connection using a UNION ALL custom sql statement. I found the attached public workbook and looked at the calculated fields and custom sql to achieve the same result with my own data source. It sounds like you are trying to do something similar.
Before, our data was as follows:
Instance ID # Date Entered Date Closed
ID-1 1/1/2013 2/15/2013
ID-2 2/1/2013 3/15/2013
ID-3 3/1/2013 4/15/2013
To have both dates on one graph (we've done both bar charts and line graphs), the UNION ALL statement has the data like this now:
Instance ID # Event Date Event Type
ID-1 1/1/2013 Entered
ID-1 2/15/2013 Closed
ID-2 2/1/2013 Entered
ID-2 3/15/2013 Closed
ID-3 3/1/2013 Entered
ID-3 4/15/2013 Closed
I made a variety of calculated fields to show the count entered, the count closed, and the difference between the two. Then I use MONTH of event date to show what happened in each month.
Hope this helps!
tickets.twbx.zip 813.7 KB
Thank you SO much Rachel!
Your workbook example worked perfectly and I was able to reshape my data so I could get the view I was looking for! You rock! Do I get to +1 your response somehow if I'm not the original thread author?
I ran into another problem with not being able to put 2 measures as lines and 2 other measures as bars on the same graph but it seems that this is not possible from all the research I've done on the forums. Bummer.
exactly the same issue..thanks for the above guidance!!