3 Replies Latest reply: Mar 8, 2013 8:16 AM by Jonathan Brough RSS

    Updating 2 Charts with Different Date Fields Using Selections from the Master Calendar

      I'm a bit of a newbie so may be missing something here.  I hope I can explain it clearly. I have a data set that contains information on a set of processes.  Each row represents a process that has multiple time points.  The data set is like this:

      Process ID

      Area

      Start of Process Date

      Process Point 1 Date

      Process Point 2 Date

      Time to Complete Process 1

      Time to Complete Process 2

       

      6

      North Yorkshire

      03-Aug-12

      26-Sep-12

      24-Aug-12

      54

      21

      7

      West Yorkshire

      22-Aug-12

      31-Aug-12

      23-Aug-12

      9

      1

      8

      South Yorkshire

      15-Jun-12

      10-Aug-12

      12-Jun-12

      56

      -3

      I want to show two charts: one that shows the average time to complete process 1 and another that shows the average time to complete process 2.  I want these charts to update based on selections from list boxes based on the master calendar.  However, I would like the chart for process 1 to allocate the process to a time period based on Process Point 1 Date and the chart for process 2 to allocate the process to a time period based on Process Point 2 Date.  As you can see the  dates for process 1 and process 2 are in the same table.  The process ID is my primary key to other tables.

      I am not sure whether I need to build something into my data model to handle this or whether this is better managed in an if statement or set analysis in the expression for the chart.  I am assuming it would have to be within the expression as each object would need to define which date it is constrained on in relation to the master calendar. I have built a replica .qvw and have attached this and the data.  I tried using:

      =avg(if(num([Process Point 1 Date])=num(CalendarDate),[Time to Complete Process 1]))

       

      in the expression for the chart for process 1 to try to link the process 1 end date to the calendar date, but I end up with an "out of object memory" message.

      What is attached is just a small subset of the actual data I want to use.  In reality one table could have up to  5 different process points and dates so any solution would need to be scalable.

      I hope someone can help.  Thanks in advance for your time.

       

       

      Vicki