Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
example of the if statement for Created Tickets:
count(distinct if([TicketCreateDate]= CalendarDate,[Ticket Number]))
and for Closed Tickets
count(distinct if([TicketCloseDate]= CalendarDate,[Ticket Number]))
example of chart
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.
I suspect there is a good solution to this.
Jim
Either modify an existing table or create a new table with something like this:
TicketDates:
Load distinct [Ticket Number], TicketCreateDate as CalendarDate, 0 as OpenClose
from ...tickettable... ;
Load distinct [Ticket Number], TicketCloseDate as CalendarDate, 1 as OpenClose
from ...tickettable... ;
You can then use the new OpenClose field in the expressions:
Opened SRs: count({<OpenClose={0}>} [Ticket Number])
Closed SRs: count({<OpenClose={1}>} [Ticket Number])
0 and 1 are just examples. Strings like 'Open' and 'Close' work too, but perhaps a little slower.
Either modify an existing table or create a new table with something like this:
TicketDates:
Load distinct [Ticket Number], TicketCreateDate as CalendarDate, 0 as OpenClose
from ...tickettable... ;
Load distinct [Ticket Number], TicketCloseDate as CalendarDate, 1 as OpenClose
from ...tickettable... ;
You can then use the new OpenClose field in the expressions:
Opened SRs: count({<OpenClose={0}>} [Ticket Number])
Closed SRs: count({<OpenClose={1}>} [Ticket Number])
0 and 1 are just examples. Strings like 'Open' and 'Close' work too, but perhaps a little slower.
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
12345 5
12345 5
54321 3
So the average of these 2 incidents is 5+3 / 2 = 4 NOT 5+5+3 /3 = 4.3
Any ideas on that would be appreciated.
Basically I want to
average (Field X)
but only selecting distinct Incident Numbers
Hi Jim,
You can also use avg(distinct Field X) as one of your measure.
Regards
Nitin Guta