Filter Accross Date Range  Different Filter Value for Different Dates
Author 
Message 
Andrew Pins #1 / 10

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 


Chris Webb [MS #2 / 10

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


Andrew Pins #3 / 10

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 


Chris Webb [MS #4 / 10

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 


Andrew Pins #5 / 10

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 


Chris Webb [MS #6 / 10

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 hardcoded 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
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 


Andrew Pins #7 / 10

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 hardcoded 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 


Andrew Pins #8 / 10

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 hardcoded 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 


Chris Webb [MS #9 / 10

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
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, 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 hardcoded 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 


Andrew Pins #10 / 10

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 preNewsgroup 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 hardcoded 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 


