How to select data from one set?

[#1]

Hello,

Is that possible to selected  one set of data out of 2 or more sets depends on the date selection?

For ex: I have one table Called Rotas having following columns

Rotas Table:

Areacode,StartDate, EndDate, VehicleType, h1, h2,h3,Rota_Total

211,1/1/2000,1/20/2012,DCA, 2,3,4,9

211,21/1/2013, 12/1/3000,DCA,3,2,1,6

For each area code, i have two set of the data... but I want to select only one set....depends on the user selection between StartDate and EndDate

Iam using the following expression, which gives the sum of Rota_Total from both the sets:

=Sum({$<Vehicle_Type={'DCA','DECA'}>}   Rota_Total)

According to previous data, the outout is 15 (9+6). But i want to get the Rota_Total based on Date selected by user, that falls between StartDate and EndDate.

if user selects any date before 1/20/2013 the output should be 9

if user select any date after 1/21/2013, the output should be 6

May be in future, another data set can be added.

This Rotas table is linked to another table DemandPerformance .

and DemandPerformance table is linked to Date_Dimension Table.

DemandPerformance transaction table is acting like bridge between Date_Dimension and Rotas table.

i appreciate your great help?

-- SD

[#2 · Accepted solution]

Hi

There are a couple of ways to allow your user to make the choice.

Firstly, the simple approach is to create a concatenated field with the Start and End Date of the date range.This field could then be selected by the user to choose whether they want to see their results for the period "1st Jan 2000 - 20th January 2012" or for "21st January 2012 to 1st December 3000".  The Selection Box should have the Option 'ALWAYS ONE SELECTED' ticked to ensure you never double count.

Alternatively, if you want to a little more of a jazzy solution and you are comfortable with a little bit of Set Analysis (bearing in the mind the performance toll); you could create a data island in the form of a calendar.  i.e. a Calendar table that does not join your main model's tables (see the community for loads of examples on how to do this).  The user could then select a date from this 'free' calendar, which would drive your formula with some set analysis such as below;

sum( {< StartDate = {"<=$(=CalendarDateSelected)"}  , EndDate = {">=$(=CalendarDateSelected)"}  >} Rota_Total)

Where CalendarDateSelected is the name of the date in the free calendar.

This has the drawback of needing to be implemented in all your formula but would be a slightly smoother experience for your users.

I'm sure there are other options out there too.  I hope this helps and please let me know if this was useful or even if solves your problem.

Regards

Steve

[#3]

Stephen,

Yes, It helped me to solve the issue. Thank you very much for your timely help.

Regards

Siva