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

[#1]

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

[Anonymous]

I think that becuase your users can alter the timeframe the TimeToComplete fields in the dataodel can't be used. I would do the following:

1. ensure you have a Master calendar built into your datamodel (a search on this forum will find the code for that)

2. set up two date variables: vProcess1Date and vProcess2Date

3. set up two calendar objects, one for each of these two variables

4. have dimensions of Process If, Area etc.

5. for the expressions, compare the ProcessDate fields in the datamodel to these calendar variables. Something like:

Not too sure how to do it in an expression just now, but I can think it through later if you have a problem with it.

Jonathan

[#3]

Thanks Jonathan,

This is definitely moving in the right direction.  I implemented your solution then altered it slightly so I had only one variable vEndDate that I referred to in both charts.  This means I can select a date from the slider/calendar object and both charts update.  I'm currently looking at how to accurately select ranges of dates in these objects.  I will come back if I need further assistance.

Thank you!

Vicki

[Anonymous]

You can use a Slider (the same as the Calendar, but with a different setting on the General tab) and set it (also on the General tab) to have Multi value and set a start and end date.

Jonathan