How to chart 2 independent measures on a common dimension?
I have a problem charting 2 measures on a common dimension. I have work tickets that are created by customers and then worked on, and closed some time (days) later.
I need a chart that show both of these volumes on a common month dimension. Since I cannot select Create Date nor Closed date, as this would show incorrect data, I created an independent calendar and use an if statement to count the Created and the Closed tickets. So I select a period ( in the below example Month) on the independent calendar to get the chart results.
Now this works perfectly fine. The problem is that when I have a large set of data 5 to 10 million records it is incredible slow. The reason is that it compares every record against every Selected Calendar date for closed AND open.
So my question is how can I accomplish the same thing but with a faster response time? I did try set analysis but it did not seem to be faster, but perhaps I implemented it incorrectly.
Thank you Gysbert. This answer is correct. However I have a follow on question. For counts this works fine and it speed it up a great deal. But I neglected to say that I also want averages of certain fields. Averages work with this also provided that Incidents Numbers only have one line each. But I have duplicate Incidents Numbers and this is ok for counts as I can count distinct.
But if I want to average a field I need to average it against each unique Incident number. I can't seem to get the logic for averaging (using aggr) against a unique field entry.
for instance if I have
Incident Number Field X
So the average of these 2 incidents is 5+3 / 2 = 4 NOT 5+5+3 /3 = 4.3