Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm currently trying to create a line chart that will show as a percentage the accumulated number of an event type over the accumulated total of events to date. Basically, on any given date I want to see the percentage of all event types that have occurred up to the given date.
I've attached a .qvf file with sample data to showcase what I mean. For testing purposes, I am just using a straight table that I created. I was able to figure out parts of the problem but then I realized I was missing something.
As you can see above, I have a straight table with two dimensions: EventDate and EventType. For each of these I have calculated the accumulated total number of events that have occurred for each event type at each given date and I have calculated the accumulated total number of events for each date. Looking at the first date, you can see that 100% of the event types by that date were 1. For the second date, 50% of the total events were 1, 25% were 3, and 25% were 5. The idea is that you will be able to see how much each event type builds in to the total. And every date should add up to 100%.
The problem that I found was that by 7/23/2019, the expected values should be:
| Event Type | Count to Date | Accumulated % of Total |
| 1 | 2 | 40% |
| 2 | 1 | 20% |
| 5 | 1 | 20% |
| 7 | 1 | 20% |
But instead, I only see Event Type 7 at 20%. In order to get the behavior that I want, I need to repeat every event type that has occurred so far as a dimension. The first thing that came to mind was to have a cartesian join, but on my actual table I am dealing with a lot more dates and a lot more event types, which would result in way too much data. And so I was wondering if it was possible to create a similar effect within a chart? In my chart, there will be a much smaller amount of event types and dates to work with, so I'm not too worried about that.
BONUS: If anyone can tell me why the Total to Date expression sometimes returns the accumulation in the last row instead of the first row, I would appreciate it 😁. The solution I had was to do if(Not IsNull(Top(...)), Top(...), Bottom(...)). It just seems that I'm not understanding something about it.
