Is it possible to get a cartesian product in a chart expression?

dselgo_eidex — Sat, 16 Nov 2024 05:07:47 GMT

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.

Straight Table

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.

topic Is it possible to get a cartesian product in a chart expression? in App Development

Is it possible to get a cartesian product in a chart expression?