3 Replies Latest reply: Dec 4, 2013 1:41 AM by Nitin Gupta RSS

    How to chart 2 independent measures on a common dimension?

    jim OConnor

      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

       

      created-closed.JPG.jpg

      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

        • Re: How to chart 2 independent measures on a common dimension?
          Gysbert Wassenaar

          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.

            • Re: How to chart 2 independent measures on a common dimension?
              jim OConnor

              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