> Below is simple sample of what I am trying to achieve.
> I need to filter out a row if the value ([Measures].[Unit Sales]) for
> any of the different conditions for different dates are met.

> In the sample below 'set [F1]' filters for ONLY ONE Date using 'member
> [Measures].[Filter]' which specifies the different conditions for
> different dates (there are only two conditons here for simplicity
> sake).

> I need to filter across the complete date range using a different
> filter value specified in 'member [Measures].[Filter]'.

> with

> member [Measures].[Filter] as ' iif(
> [Time].[1998].[Q2].Dimension.CurrentMember IS
> [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> [Measures].[Unit Sales]),
>   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> [Measures].[Unit Sales]), 1))'

> set [dates] as

> Sales],[Measures].[Filter]})'
> set [rows] as 'Descendants([Product].[All
> Products].[Drink].[Beverages],1)'

> set [F1] as 'filter(  [rows],(
> [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> select [dates] on columns,
> non empty { [F1] } on rows
> From [Sales]

> Thanks in advance
> AndrewP

> Hi Andrew,

> I'm not sure, but I think this is what you're after - you want to filter the
> members in F1 across all combinations of Time and Measures.Filter, do you?
> You can use AND and OR in your filter statements to achieve this if you know
> what is going to be on columns in advance, for example with something like

> FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> HTH,

> Chris

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.

> > Below is simple sample of what I am trying to achieve.
> > I need to filter out a row if the value ([Measures].[Unit Sales]) for
> > any of the different conditions for different dates are met.

> > In the sample below 'set [F1]' filters for ONLY ONE Date using 'member
> > [Measures].[Filter]' which specifies the different conditions for
> > different dates (there are only two conditons here for simplicity
> > sake).

> > I need to filter across the complete date range using a different
> > filter value specified in 'member [Measures].[Filter]'.

> > with

> > member [Measures].[Filter] as ' iif(
> > [Time].[1998].[Q2].Dimension.CurrentMember IS
> > [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> > [Measures].[Unit Sales]),
> >   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> > [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> > [Measures].[Unit Sales]), 1))'

> > set [dates] as
>  'crossjoin({[Time].[1997].[Q1]:[Time].[1998].[Q2]},{[Measures].[Unit
> > Sales],[Measures].[Filter]})'
> > set [rows] as 'Descendants([Product].[All
> > Products].[Drink].[Beverages],1)'

> > set [F1] as 'filter(  [rows],(
> > [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> > select [dates] on columns,
> > non empty { [F1] } on rows
> > From [Sales]

> > Thanks in advance
> > AndrewP

> Thankyou very much for your prompt reply Chris.
> I should have mentioned that my application allows the user to select
> a Time Dimension Range i.e. A start Time and a End Time. I cannot rely
> on the time dimension members being consecutive, So I need to some how
> use the Time Range they have selected and filter specific Time values
> (hence using the Lag() function) for different values using the
> [Measures].[Filter] (which will check the current Time Dimension
> member and apply the appropriate filter).

> Hope this makes sense.

> Regards
> AndrewP

> > Hi Andrew,

> > I'm not sure, but I think this is what you're after - you want to filter
the
> > members in F1 across all combinations of Time and Measures.Filter, do
you?
> > You can use AND and OR in your filter statements to achieve this if you
know
> > what is going to be on columns in advance, for example with something
like

> > FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> > (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> > ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> > HTH,

> > Chris

> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.

> > > Below is simple sample of what I am trying to achieve.
> > > I need to filter out a row if the value ([Measures].[Unit Sales]) for
> > > any of the different conditions for different dates are met.

> > > In the sample below 'set [F1]' filters for ONLY ONE Date using 'member
> > > [Measures].[Filter]' which specifies the different conditions for
> > > different dates (there are only two conditons here for simplicity
> > > sake).

> > > I need to filter across the complete date range using a different
> > > filter value specified in 'member [Measures].[Filter]'.

> > > with

> > > member [Measures].[Filter] as ' iif(
> > > [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> > > [Measures].[Unit Sales]),
> > >   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> > > [Measures].[Unit Sales]), 1))'

> > > set [dates] as
> >  'crossjoin({[Time].[1997].[Q1]:[Time].[1998].[Q2]},{[Measures].[Unit
> > > Sales],[Measures].[Filter]})'
> > > set [rows] as 'Descendants([Product].[All
> > > Products].[Drink].[Beverages],1)'

> > > set [F1] as 'filter(  [rows],(
> > > [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> > > select [dates] on columns,
> > > non empty { [F1] } on rows
> > > From [Sales]

> > > Thanks in advance
> > > AndrewP

> Can you give me an example of a resultset before and after filtering, so I
> can understand exactly what it is you want to do?

> Chris

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.

> > Thankyou very much for your prompt reply Chris.
> > I should have mentioned that my application allows the user to select
> > a Time Dimension Range i.e. A start Time and a End Time. I cannot rely
> > on the time dimension members being consecutive, So I need to some how
> > use the Time Range they have selected and filter specific Time values
> > (hence using the Lag() function) for different values using the
> > [Measures].[Filter] (which will check the current Time Dimension
> > member and apply the appropriate filter).

> > Hope this makes sense.

> > Regards
> > AndrewP

> > > Hi Andrew,

> > > I'm not sure, but I think this is what you're after - you want to filter
>  the
> > > members in F1 across all combinations of Time and Measures.Filter, do
>  you?
> > > You can use AND and OR in your filter statements to achieve this if you
>  know
> > > what is going to be on columns in advance, for example with something
>  like

> > > FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> > > (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> > > ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> > > HTH,

> > > Chris

> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > Below is simple sample of what I am trying to achieve.
> > > > I need to filter out a row if the value ([Measures].[Unit Sales]) for
> > > > any of the different conditions for different dates are met.

> > > > In the sample below 'set [F1]' filters for ONLY ONE Date using 'member
> > > > [Measures].[Filter]' which specifies the different conditions for
> > > > different dates (there are only two conditons here for simplicity
> > > > sake).

> > > > I need to filter across the complete date range using a different
> > > > filter value specified in 'member [Measures].[Filter]'.

> > > > with

> > > > member [Measures].[Filter] as ' iif(
> > > > [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> > > > [Measures].[Unit Sales]),
> > > >   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> > > > [Measures].[Unit Sales]), 1))'

> > > > set [dates] as
>  'crossjoin({[Time].[1997].[Q1]:[Time].[1998].[Q2]},{[Measures].[Unit
> > > > Sales],[Measures].[Filter]})'
> > > > set [rows] as 'Descendants([Product].[All
> > > > Products].[Drink].[Beverages],1)'

> > > > set [F1] as 'filter(  [rows],(
> > > > [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> > > > select [dates] on columns,
> > > > non empty { [F1] } on rows
> > > > From [Sales]

> > > > Thanks in advance
> > > > AndrewP

} on rows

> with
> set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> set [rows] as 'Descendants([Product].[All
> Products].[Drink].[Beverages],1)'

> select [dates] on columns,
> non empty { [rows] } on rows
> From [Sales]

> where  ([Measures].[Unit Sales])

> Returns the following resultset (on Foodmart 2000)

>              Q1    Q2    Q3    Q4
> Carbonated  789   856   882   880
> Drinks      617   569   562   721
> Hot        1090  1037  1037  1137
> Pure Juice  837   805   895   859

> My application allows the user to select a Time Range (Start Time and
> End Time),
> The user can also enter Three values which are respectively applied to
> the last three time periods of all rows in the resultset. If any of
> the corresponding values in the result set are greater than the user
> defined period value then the row will not be in the final result set.

> e.g. the user has selected the above time range and the following last
> three Period filter values
> Period 1 (in this case Q4) value = 1200
> Period 2 (in this case Q3) value = 1200
> Period 3 (in this case Q2) value = 1036

> The required resultset would be:

>              Q1    Q2    Q3    Q4
> Carbonated  789   856   882   880
> Drinks      617   569   562   721
> Pure Juice  837   805   895   859

> because for row 'Hot', period 'Q2', the value is 1037 which is greater
> than the value the user entered for this period (1036), periods Q3 and
> Q4 are less than the period and met the requirements as do the all
> periods for the other rows.

> Keep in mind that I will be building this MDX string in VB and only
> know the MDX values for Time Start and Time End supplied by the
> application (Cube, Measure and Product dimension are constant).

> Thanks

> > Can you give me an example of a resultset before and after filtering, so
I
> > can understand exactly what it is you want to do?

> > Chris

> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.

> > > Thankyou very much for your prompt reply Chris.
> > > I should have mentioned that my application allows the user to select
> > > a Time Dimension Range i.e. A start Time and a End Time. I cannot rely
> > > on the time dimension members being consecutive, So I need to some how
> > > use the Time Range they have selected and filter specific Time values
> > > (hence using the Lag() function) for different values using the
> > > [Measures].[Filter] (which will check the current Time Dimension
> > > member and apply the appropriate filter).

> > > Hope this makes sense.

> > > Regards
> > > AndrewP

> > > > Hi Andrew,

> > > > I'm not sure, but I think this is what you're after - you want to
filter
> >  the
> > > > members in F1 across all combinations of Time and Measures.Filter,
do
> >  you?
> > > > You can use AND and OR in your filter statements to achieve this if
you
> >  know
> > > > what is going to be on columns in advance, for example with
something
> >  like

> > > > FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales])
<
> > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> > > > (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> > > > ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> > > > HTH,

> > > > Chris

> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> >  rights.

> > > > > Below is simple sample of what I am trying to achieve.
> > > > > I need to filter out a row if the value ([Measures].[Unit Sales])
for
> > > > > any of the different conditions for different dates are met.

> > > > > In the sample below 'set [F1]' filters for ONLY ONE Date using
'member
> > > > > [Measures].[Filter]' which specifies the different conditions for
> > > > > different dates (there are only two conditons here for simplicity
> > > > > sake).

> > > > > I need to filter across the complete date range using a different
> > > > > filter value specified in 'member [Measures].[Filter]'.

> > > > > with

> > > > > member [Measures].[Filter] as ' iif(
> > > > > [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > > [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> > > > > [Measures].[Unit Sales]),
> > > > >   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > > [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> > > > > [Measures].[Unit Sales]), 1))'

> > > > > set [dates] as
> >  'crossjoin({[Time].[1997].[Q1]:[Time].[1998].[Q2]},{[Measures].[Unit
> > > > > Sales],[Measures].[Filter]})'
> > > > > set [rows] as 'Descendants([Product].[All
> > > > > Products].[Drink].[Beverages],1)'

> > > > > set [F1] as 'filter(  [rows],(
> > > > > [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> > > > > select [dates] on columns,
> > > > > non empty { [F1] } on rows
> > > > > From [Sales]

> > > > > Thanks in advance
> > > > > AndrewP

} on rows

> Hi Andrew,

> I still have the nagging feeling I'm missing something, but here is some MDX
> that does what I think you want:

> with
> set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> set [rows] as 'Descendants([Product].[All Products].[Drink].[Beverages],1)'
> member measures.FILTERVAL as '
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> )
>  )'

> select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit Sales]}) on
> columns,
> non empty {
> FILTER(
> [rows],
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))
> AND
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-2))
> AND
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-3))
> )
> } on rows
> From [Sales]

> It's not reliant on the dates being consecutive - it just needs at least
> three dates in the DATES set. The calc measure FILTERVAL assigns the values
> you gave depending on what is the currentmember on TIME, working backwards
> from the last member in the DATES set, and the filter expression similarly
> compares Unit Sales with FilterVal.

> Of course, this is hard-coded to only work with there being three values
> assigned by the user for the last three time periods. Tell me if I've missed
> something here....

> Chris

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.

> > with
> > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > set [rows] as 'Descendants([Product].[All
> > Products].[Drink].[Beverages],1)'

> > select [dates] on columns,
> > non empty { [rows] } on rows
> > From [Sales]

> > where  ([Measures].[Unit Sales])

> > Returns the following resultset (on Foodmart 2000)

> >              Q1    Q2    Q3    Q4
> > Carbonated  789   856   882   880
> > Drinks      617   569   562   721
> > Hot        1090  1037  1037  1137
> > Pure Juice  837   805   895   859

> > My application allows the user to select a Time Range (Start Time and
> > End Time),
> > The user can also enter Three values which are respectively applied to
> > the last three time periods of all rows in the resultset. If any of
> > the corresponding values in the result set are greater than the user
> > defined period value then the row will not be in the final result set.

> > e.g. the user has selected the above time range and the following last
> > three Period filter values
> > Period 1 (in this case Q4) value = 1200
> > Period 2 (in this case Q3) value = 1200
> > Period 3 (in this case Q2) value = 1036

> > The required resultset would be:

> >              Q1    Q2    Q3    Q4
> > Carbonated  789   856   882   880
> > Drinks      617   569   562   721
> > Pure Juice  837   805   895   859

> > because for row 'Hot', period 'Q2', the value is 1037 which is greater
> > than the value the user entered for this period (1036), periods Q3 and
> > Q4 are less than the period and met the requirements as do the all
> > periods for the other rows.

> > Keep in mind that I will be building this MDX string in VB and only
> > know the MDX values for Time Start and Time End supplied by the
> > application (Cube, Measure and Product dimension are constant).

> > Thanks

> > > Can you give me an example of a resultset before and after filtering, so
>  I
> > > can understand exactly what it is you want to do?

> > > Chris

> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > Thankyou very much for your prompt reply Chris.
> > > > I should have mentioned that my application allows the user to select
> > > > a Time Dimension Range i.e. A start Time and a End Time. I cannot rely
> > > > on the time dimension members being consecutive, So I need to some how
> > > > use the Time Range they have selected and filter specific Time values
> > > > (hence using the Lag() function) for different values using the
> > > > [Measures].[Filter] (which will check the current Time Dimension
> > > > member and apply the appropriate filter).

> > > > Hope this makes sense.

> > > > Regards
> > > > AndrewP

> > > > > Hi Andrew,

> > > > > I'm not sure, but I think this is what you're after - you want to
>  filter
>  the
> > > > > members in F1 across all combinations of Time and Measures.Filter,
>  do
>  you?
> > > > > You can use AND and OR in your filter statements to achieve this if
>  you
>  know
> > > > > what is going to be on columns in advance, for example with
>  something
>  like

> > > > > FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales])
>  <
> > > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> > > > > (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> > > > > ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> > > > > HTH,

> > > > > Chris

> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > > > Below is simple sample of what I am trying to achieve.
> > > > > > I need to filter out a row if the value ([Measures].[Unit Sales])
>  for
> > > > > > any of the different conditions for different dates are met.

> > > > > > In the sample below 'set [F1]' filters for ONLY ONE Date using
>  'member
> > > > > > [Measures].[Filter]' which specifies the different conditions for
> > > > > > different dates (there are only two conditons here for simplicity
> > > > > > sake).

> > > > > > I need to filter across the complete date range using a different
> > > > > > filter value specified in 'member [Measures].[Filter]'.

> > > > > > with

> > > > > > member [Measures].[Filter] as ' iif(
> > > > > > [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > > > [Time].[1998].[Q2].Lag(3), ( [Time].[1998].[Q2].Lag(2),
> > > > > > [Measures].[Unit Sales]),
> > > > > >   iif( [Time].[1998].[Q2].Dimension.CurrentMember IS
> > > > > > [Time].[1998].[Q2].Lag(4), ( [Time].[1998].[Q2].Lag(3),
> > > > > > [Measures].[Unit Sales]), 1))'

> > > > > > set [dates] as
>  'crossjoin({[Time].[1997].[Q1]:[Time].[1998].[Q2]},{[Measures].[Unit
> > > > > > Sales],[Measures].[Filter]})'
> > > > > > set [rows] as 'Descendants([Product].[All
> > > > > > Products].[Drink].[Beverages],1)'

> > > > > > set [F1] as 'filter(  [rows],(
> > > > > > [Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales]) <
> > > > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter]) )'

> > > > > > select [dates] on columns,
> > > > > > non empty { [F1] } on rows
> > > > > > From [Sales]

> > > > > > Thanks in advance
> > > > > > AndrewP

> Hi Chris,
> thanks for all your effort,
> The example you have given me is filtering, in this case (Q4).
> Modifying your example slightly, the MDX below gives the
> Filter results I am looking for.

> with
> set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> set [rows] as 'Descendants([Product].[All
> Products].[Drink].[Beverages],1)'
> member measures.FILTERVAL as '
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> )
>  )'

> select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit
> Sales]}) on
> columns,
> non empty {
> FILTER(
> [rows],
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
> PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-1))
> AND
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-2))<(MEASURES.[FILTERVAL],
> PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-2))
> AND
> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-3))
> )
> } on rows
> From [Sales]

> The problem for me is that I need to filter the current Product and
> Time Dimension crossjoin value. When we Filter [rows] we have AND
> statements for a specific time dimension.

> (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> PRODUCT.CURRENTMEMBER,
> DATES.ITEM(COUNT(DATES)-3))

> I need to somehow use the DATES.CurrentMember like we have for the
> PRODUCT.CURRENTMEMBER, then FILTERVAL can use the DATE.CurrentMember

> This is difficult to explain and maybe I am going about this the wrong
> way, but thanks again for your help. I will keep trying!

> Andrew

> > Hi Andrew,

> > I still have the nagging feeling I'm missing something, but here is some MDX
> > that does what I think you want:

> > with
> > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > set [rows] as 'Descendants([Product].[All Products].[Drink].[Beverages],1)'
> > member measures.FILTERVAL as '
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> > )
> >  )'

> > select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit Sales]}) on
> > columns,
> > non empty {
> > FILTER(
> > [rows],
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))
> > AND
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-2))
> > AND
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-3))
> > )
> > } on rows
> > From [Sales]

> > It's not reliant on the dates being consecutive - it just needs at least
> > three dates in the DATES set. The calc measure FILTERVAL assigns the values
> > you gave depending on what is the currentmember on TIME, working backwards
> > from the last member in the DATES set, and the filter expression similarly
> > compares Unit Sales with FilterVal.

> > Of course, this is hard-coded to only work with there being three values
> > assigned by the user for the last three time periods. Tell me if I've missed
> > something here....

> > Chris

> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.

> > > with
> > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > set [rows] as 'Descendants([Product].[All
> > > Products].[Drink].[Beverages],1)'

> > > select [dates] on columns,
> > > non empty { [rows] } on rows
> > > From [Sales]

> > > where  ([Measures].[Unit Sales])

> > > Returns the following resultset (on Foodmart 2000)

> > >              Q1    Q2    Q3    Q4
> > > Carbonated  789   856   882   880
> > > Drinks      617   569   562   721
> > > Hot        1090  1037  1037  1137
> > > Pure Juice  837   805   895   859

> > > My application allows the user to select a Time Range (Start Time and
> > > End Time),
> > > The user can also enter Three values which are respectively applied to
> > > the last three time periods of all rows in the resultset. If any of
> > > the corresponding values in the result set are greater than the user
> > > defined period value then the row will not be in the final result set.

> > > e.g. the user has selected the above time range and the following last
> > > three Period filter values
> > > Period 1 (in this case Q4) value = 1200
> > > Period 2 (in this case Q3) value = 1200
> > > Period 3 (in this case Q2) value = 1036

> > > The required resultset would be:

> > >              Q1    Q2    Q3    Q4
> > > Carbonated  789   856   882   880
> > > Drinks      617   569   562   721
> > > Pure Juice  837   805   895   859

> > > because for row 'Hot', period 'Q2', the value is 1037 which is greater
> > > than the value the user entered for this period (1036), periods Q3 and
> > > Q4 are less than the period and met the requirements as do the all
> > > periods for the other rows.

> > > Keep in mind that I will be building this MDX string in VB and only
> > > know the MDX values for Time Start and Time End supplied by the
> > > application (Cube, Measure and Product dimension are constant).

> > > Thanks

> > > > Can you give me an example of a resultset before and after filtering, so
>  I
> > > > can understand exactly what it is you want to do?

> > > > Chris

> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > > Thankyou very much for your prompt reply Chris.
> > > > > I should have mentioned that my application allows the user to select
> > > > > a Time Dimension Range i.e. A start Time and a End Time. I cannot rely
> > > > > on the time dimension members being consecutive, So I need to some how
> > > > > use the Time Range they have selected and filter specific Time values
> > > > > (hence using the Lag() function) for different values using the
> > > > > [Measures].[Filter] (which will check the current Time Dimension
> > > > > member and apply the appropriate filter).

> > > > > Hope this makes sense.

> > > > > Regards
> > > > > AndrewP

> > > > > > Hi Andrew,

> > > > > > I'm not sure, but I think this is what you're after - you want to
> >  filter
> >  the
> > > > > > members in F1 across all combinations of Time and Measures.Filter,
> >  do
> >  you?
> > > > > > You can use AND and OR in your filter statements to achieve this if
> >  you
> >  know
> > > > > > what is going to be on columns in advance, for example with
> >  something
> >  like

> > > > > > FILTER([ROWS], (([Time].[1998].[Q2].Lag(3),[Measures].[Unit Sales])
>  <
> > > > > > ([Time].[1998].[Q2].Lag(3), [Measures].[Filter])) AND
> > > > > > (([Time].[1998].[Q2].Lag(4),[Measures].[Unit Sales]) <
> > > > > > ([Time].[1998].[Q2].Lag(4), [Measures].[Filter])))

> > > > > > HTH,

> > > > > > Chris

> > > > > > --
> > > > > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > > > > Below is simple sample of what I am trying to achieve.
> > > > > > > I need to filter out a row if the value ([Measures].[Unit Sales])
>  for
> > > > > > > any of the different conditions for different dates are met.

> > > > > > > In the sample below 'set [F1]' filters for ONLY ONE Date using
>  'member
> > > > > > > [Measures].[Filter]' which specifies the different conditions for
> > > > > > > different dates (there are only two conditons here for simplicity
> > > > > > > sake).

> > > > > > > I need to filter across the complete date range using a different
> > > > > > > filter value specified in 'member

> Following on, this MDX produces the filtering I require:

> with
> set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> set [rows] as 'Descendants([Product].[All
> Products].[Drink].[Beverages],1)'
> member measures.FILTERVAL as '
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1036)
> )
>  )'

> set [cross] as ' CROSSJOIN([dates],[rows])'

> set [f1] as 'Filter([cross],(MEASURES.[UNIT SALES],TIME.CURRENTMEMBER,
> Product.currentmember) < (MEASURES.[FILTERVAL],TIME.CURRENTMEMBER,
> Product.currentmember) )'

> select {[Measures].[Unit Sales],MEASURES.[FILTERVAL]} on
> columns,
> non empty {
> [f1]} on rows

> From [Sales]

> The only problem now is the layout of the results!
> Hope this helps in understanding my problem.

> Regards
> Andrew

> > Hi Chris,
> > thanks for all your effort,
> > The example you have given me is filtering, in this case (Q4).
> > Modifying your example slightly, the MDX below gives the
> > Filter results I am looking for.

> > with
> > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > set [rows] as 'Descendants([Product].[All
> > Products].[Drink].[Beverages],1)'
> > member measures.FILTERVAL as '
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> > )
> >  )'

> > select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit
> > Sales]}) on
> > columns,
> > non empty {
> > FILTER(
> > [rows],
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
> > PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-1))
> > AND
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-2))<(MEASURES.[FILTERVAL],
> > PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-2))
> > AND
> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> > PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-3))
> > )
> > } on rows
> > From [Sales]

> > The problem for me is that I need to filter the current Product and
> > Time Dimension crossjoin value. When we Filter [rows] we have AND
> > statements for a specific time dimension.

> > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> > PRODUCT.CURRENTMEMBER,
> > DATES.ITEM(COUNT(DATES)-3))

> > I need to somehow use the DATES.CurrentMember like we have for the
> > PRODUCT.CURRENTMEMBER, then FILTERVAL can use the DATE.CurrentMember

> > This is difficult to explain and maybe I am going about this the wrong
> > way, but thanks again for your help. I will keep trying!

> > Andrew

> > > Hi Andrew,

> > > I still have the nagging feeling I'm missing something, but here is
some MDX
> > > that does what I think you want:

> > > with
> > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > set [rows] as 'Descendants([Product].[All

> > > member measures.FILTERVAL as '
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> > > )
> > >  )'

> > > select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit
Sales]}) on
> > > columns,
> > > non empty {
> > > FILTER(
> > > [rows],
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],

> > > DATES.ITEM(COUNT(DATES)-1))
> > > AND
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],

> > > DATES.ITEM(COUNT(DATES)-2))
> > > AND
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],

> > > DATES.ITEM(COUNT(DATES)-3))
> > > )
> > > } on rows
> > > From [Sales]

> > > It's not reliant on the dates being consecutive - it just needs at
least
> > > three dates in the DATES set. The calc measure FILTERVAL assigns the
values
> > > you gave depending on what is the currentmember on TIME, working
backwards
> > > from the last member in the DATES set, and the filter expression
similarly
> > > compares Unit Sales with FilterVal.

> > > Of course, this is hard-coded to only work with there being three
values
> > > assigned by the user for the last three time periods. Tell me if I've
missed
> > > something here....

> > > Chris

> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.

> > > > with
> > > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > > set [rows] as 'Descendants([Product].[All
> > > > Products].[Drink].[Beverages],1)'

> > > > select [dates] on columns,
> > > > non empty { [rows] } on rows
> > > > From [Sales]

> > > > where  ([Measures].[Unit Sales])

> > > > Returns the following resultset (on Foodmart 2000)

> > > >              Q1    Q2    Q3    Q4
> > > > Carbonated  789   856   882   880
> > > > Drinks      617   569   562   721
> > > > Hot        1090  1037  1037  1137
> > > > Pure Juice  837   805   895   859

> > > > My application allows the user to select a Time Range (Start Time
and
> > > > End Time),
> > > > The user can also enter Three values which are respectively applied
to
> > > > the last three time periods of all rows in the resultset. If any of
> > > > the corresponding values in the result set are greater than the user
> > > > defined period value then the row will not be in the final result
set.

> > > > e.g. the user has selected the above time range and the following
last
> > > > three Period filter values
> > > > Period 1 (in this case Q4) value = 1200
> > > > Period 2 (in this case Q3) value = 1200
> > > > Period 3 (in this case Q2) value = 1036

> > > > The required resultset would be:

> > > >              Q1    Q2    Q3    Q4
> > > > Carbonated  789   856   882   880
> > > > Drinks      617   569   562   721
> > > > Pure Juice  837   805   895   859

> > > > because for row 'Hot', period 'Q2', the value is 1037 which is
greater
> > > > than the value the user entered for this period (1036), periods Q3
and
> > > > Q4 are less than the period and met the requirements as do the all
> > > > periods for the other rows.

> > > > Keep in mind that I will be building this MDX string in VB and only
> > > > know the MDX values for Time Start and Time End supplied by the
> > > > application (Cube, Measure and Product dimension are constant).

> > > > Thanks

> > > > > Can you give me an example of a resultset before and after
filtering, so
> >  I
> > > > > can understand exactly what it is you want to do?

> > > > > Chris

> > > > > --
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> >  rights.

> > > > > > Thankyou very much for your prompt reply Chris.
> > > > > > I should have mentioned that my application allows the user to
select

> Hi Andrew,

> Two things first:
> 1) I should probably suggest an improvement to the MDX I gave you, at least
> in stylistic terms: the TAIL(MYSET,1).ITEM(0) function is probably more
> elegant than MYSET.ITEM(COUNT(MYSET)-1).
> 2) When you're evaluating rows with a filter, the currentmember function
> doesn't work as you'd think with dimensions on columns: because there could
> be more than one currentmember, it instead returns the default member of the
> dimension. This is probably the root of the problems you've been having.

> You've worked around this by putting time on the same dimension as products,
> so there is a currentmember on time when you do the filter; however, I get
> the impression you don't like this workaround or using ANDs. I've noticed
> that your last MDX expression allowed filtering on all of your time members
> and not just the last three, with the time members not in the last three
> being filtered using the third last FILTERVAL number. Is this correct? This
> would probably explain why you don't like the ANDs..!

> In this case, try something like:

> with
> member measures.FILTERVAL as '
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1036)
> )
>  )'
> set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> MEMBER MEASURES.MYTEST AS '
> STRTOVALUE(
> GENERATE([DATES],
> IIF(
> ([DATES].CURRENTMEMBER, PRODUCT.CURRENTMEMBER, MEASURES.[UNIT SALES]) <
> ([DATES].CURRENTMEMBER, PRODUCT.CURRENTMEMBER, MEASURES.[FILTERVAL]),
> "TRUE", "FALSE"
> )
> , " AND "))'

> select
> CROSSJOIN([dates],
> {[Measures].[UNIT SALES]}
> )
> on columns,
> non empty
> FILTER(
> [Product].[All Products].[Drink].[Beverages].CHILDREN,
> MEASURES.MYTEST=TRUE)
> on rows
> From [Sales]

> Hope this nails it!

> Chris

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.

> > Following on, this MDX produces the filtering I require:

> > with
> > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > set [rows] as 'Descendants([Product].[All
> > Products].[Drink].[Beverages],1)'
> > member measures.FILTERVAL as '
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1036)
> > )
> >  )'

> > set [cross] as ' CROSSJOIN([dates],[rows])'

> > set [f1] as 'Filter([cross],(MEASURES.[UNIT SALES],TIME.CURRENTMEMBER,
> > Product.currentmember) < (MEASURES.[FILTERVAL],TIME.CURRENTMEMBER,
> > Product.currentmember) )'

> > select {[Measures].[Unit Sales],MEASURES.[FILTERVAL]} on
> > columns,
> > non empty {
> > [f1]} on rows

> > From [Sales]

> > The only problem now is the layout of the results!
> > Hope this helps in understanding my problem.

> > Regards
> > Andrew

> > > Hi Chris,
> > > thanks for all your effort,
> > > The example you have given me is filtering, in this case (Q4).
> > > Modifying your example slightly, the MDX below gives the
> > > Filter results I am looking for.

> > > with
> > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > set [rows] as 'Descendants([Product].[All
> > > Products].[Drink].[Beverages],1)'
> > > member measures.FILTERVAL as '
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> > > )
> > >  )'

> > > select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit
> > > Sales]}) on
> > > columns,
> > > non empty {
> > > FILTER(
> > > [rows],
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
> > > PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))
> > > AND
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-2))<(MEASURES.[FILTERVAL],
> > > PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-2))
> > > AND
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> > > PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-3))
> > > )
> > > } on rows
> > > From [Sales]

> > > The problem for me is that I need to filter the current Product and
> > > Time Dimension crossjoin value. When we Filter [rows] we have AND
> > > statements for a specific time dimension.

> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-3))<(MEASURES.[FILTERVAL],
> > > PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-3))

> > > I need to somehow use the DATES.CurrentMember like we have for the
> > > PRODUCT.CURRENTMEMBER, then FILTERVAL can use the DATE.CurrentMember

> > > This is difficult to explain and maybe I am going about this the wrong
> > > way, but thanks again for your help. I will keep trying!

> > > Andrew

> > > > Hi Andrew,

> > > > I still have the nagging feeling I'm missing something, but here is
>  some MDX
> > > > that does what I think you want:

> > > > with
> > > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > > set [rows] as 'Descendants([Product].[All
>  Products].[Drink].[Beverages],1)'
> > > > member measures.FILTERVAL as '
> > > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-1), 1200,
> > > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-2), 1200,
> > > > IIF(TIME.CURRENTMEMBER IS DATES.ITEM(COUNT(DATES)-3), 1036,1)
> > > > )
> > > >  )'

> > > > select CROSSJOIN([dates], {MEASURES.FILTERVAL,[Measures].[Unit
>  Sales]}) on
> > > > columns,
> > > > non empty {
> > > > FILTER(
> > > > [rows],
> > > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
>  PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-1))
> > > > AND
> > > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
>  PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-2))
> > > > AND
> > > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],
>  PRODUCT.CURRENTMEMBER,
> > > > DATES.ITEM(COUNT(DATES)-3))
> > > > )
> > > > } on rows
> > > > From [Sales]

> > > > It's not reliant on the dates being consecutive - it just needs at
>  least
> > > > three dates in the DATES set. The calc measure FILTERVAL assigns the
>  values
> > > > you gave depending on what is the currentmember on TIME, working
>  backwards
> > > > from the last member in the DATES set, and the filter expression
>  similarly
> > > > compares Unit Sales with FilterVal.

> > > > Of course, this is hard-coded to only work with there being three
>  values
> > > > assigned by the user for the last three time periods. Tell me if I've
>  missed
> > > > something here....

> > > > Chris

> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
>  rights.

> > > > > with
> > > > > set [dates] as '{[Time].[1997].[Q1]:[Time].[1997].[Q4]}'
> > > > > set [rows] as 'Descendants([Product].[All
> > > > > Products].[Drink].[Beverages],1)'

> > > > > select [dates] on columns,
> > > > > non empty { [rows] } on rows
> > > > > From [Sales]

> > > > > where  ([Measures].[Unit Sales])

> > > > > Returns the following resultset (on Foodmart 2000)

> > > > >              Q1    Q2    Q3    Q4
> > > > > Carbonated  789   856   882   880
> > > > > Drinks      617   569   562   721
> > > > > Hot        1090  1037  1037  1137
> > > > > Pure Juice  837   805   895   859

> > > > > My application allows the user to select a Time Range (Start Time
>  and
> > > > > End Time),
> > > > > The user can also enter Three values which are respectively applied
>  to
> > > > > the last three time periods of all rows in the resultset. If any of
> > > > > the corresponding values in the result set are greater than the user
> > > > > defined period value then the row will not be in the final result
>  set.

> > > > > e.g. the user has selected the above time range and the following
>  last
> > > > > three Period filter values
> > > > > Period 1 (in this case Q4) value = 1200
> > > > > Period 2 (in this case Q3) value = 1200
> > > > > Period 3 (in this case Q2) value = 1036

> > > > > The required resultset would be:

> > > > >              Q1    Q2    Q3    Q4
> > > > > Carbonated  789   856   882   880
> > > > > Drinks      617   569   562   721
> > > > > Pure Juice  837   805   895   859

> > > > > because for row 'Hot', period 'Q2', the value is 1037 which is
>  greater
> > > > > than the value the user entered for this period (1036),