Filter Accross Date Range - Different Filter Value for Different Dates 
Author Message
 Filter Accross Date Range - Different Filter Value for Different Dates

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



Sun, 25 Jul 2004 07:46:09 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates

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.


Quote:
> 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

- Show quoted text -

Quote:
> 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



Sun, 25 Jul 2004 17:10:15 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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


Quote:
> 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



Mon, 26 Jul 2004 06:26:25 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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.


Quote:
> 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




Quote:
> > 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



Mon, 26 Jul 2004 16:05:43 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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


Quote:
> 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



Tue, 27 Jul 2004 06:54:33 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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))
)

Quote:
} 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.


Quote:
> 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




- Show quoted text -

Quote:
> > 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



Wed, 28 Jul 2004 00:53:12 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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))
)

Quote:
} 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


Quote:
> 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



Fri, 30 Jul 2004 07:47:17 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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

Quote:

> 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

...

read more »



Fri, 30 Jul 2004 09:11:48 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
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.


Quote:
> 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




- Show quoted text -

Quote:
> > 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




Quote:
> > > 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)'
Quote:
> > > 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,
Quote:
> > > DATES.ITEM(COUNT(DATES)-1))
> > > AND
> > > (MEASURES.[UNIT SALES], PRODUCT.CURRENTMEMBER,
> > > DATES.ITEM(COUNT(DATES)-1))<(MEASURES.[FILTERVAL],

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

PRODUCT.CURRENTMEMBER,

- Show quoted text -

Quote:
> > > 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

...

read more »



Fri, 30 Jul 2004 16:39:00 GMT
 Filter Accross Date Range - Different Filter Value for Different Dates
Hi Chris,
Yes you have nailed it and you have taught me how to use the Generate
function!
Yes I do need to filter the last three time periods with specific
values and the rest using the third period filter value.

I looked at using the Generate function pre-Newsgroup emails but
couldnt figure out how I could use it.

Thankyou again for taking the time to understand my problem and
providing a fantastic service.

AndrewP


Quote:
> 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),

...

read more »



Sat, 31 Jul 2004 07:03:31 GMT
 
 [ 10 post ] 

 Relevant Pages 

1. Merge replication applying different data filters for different subcribers

2. Dataenvironment Filter property and filtering dates

3. Best Date-Range Filter?

4. horizontal filtering by date range - deletes ALL rows on subscriber

5. Filter by Date Range for MM/DD

6. MDX Date Range Filter

7. Appending a parameter Filtered date range created by Enddate and Startdate parmaters

8. Different Date/time for different DB

9. Date format in ADO different on different servers

10. Named set to filter transactions between x date and y date

11. Filtering records by the earliest date value

12. SQL Server Date Different from NT Date


 
Powered by phpBB® Forum Software