locked
Problem of filter function? RRS feed

  • Question

  • Hi All,

    I am a newbie of MDX technique, and now I have problem to create a "Sales Gross Rate" for "Same store ONLY <same-store means the store which already open 1 year before the report date>" by country. I try to use filter function like:-

    with set [SAME STORE SET] as

    'filter([store].[geograhical].[storecode], [store status].[same store flag].&[true])'

    <FYI, hierarchy [store].[geograhical] has 3 levels as 1) Country, 2) City , 3) storecode >

    The result of store list are sorted correctly BUT the sumation figure like "Country figure/ City figure" show the <ALL STORES> value instead of <SAME STORES> value. Please kindly help. Thanks in advance.

    Thursday, April 8, 2010 10:56 AM

Answers

  • A subselect might be the easiest way of doing it. Does the following query do what you want?

    WITH MEMBER [measures].[sales growth] AS
    (
    ([measures].[net sale])

    /
    ([Measures].[Last Year Net sale])
    ) - 1

    select

     ([Store].[Country].children, [measures].[sales growth])  on 0

    from

    (SELECT

    NONEMPTY([Store].[Geographical].[storecode].members, ([measures].[net sale],[store period].[STORE STATUS].&[SAME STORE], [VW PERIOD].[YEAR - MONTH].[FIN_YEAR].&[2009].&[9])) ON 0 FROM
     [SUN WH - PnL2])
    WHERE
     [VW PERIOD].[YEAR - MONTH].[FIN_YEAR].&[2009].&[9]

    BTW, I've used the NonEmpty function instead of the Filter to find the Stores that have the Same Store status in September 2009 - hopefully it gives the same results, and it should be a little faster and more reliable than using Filter the way you were doing.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    • Marked as answer by Raymond-Lee Friday, April 16, 2010 2:50 AM
    Friday, April 9, 2010 11:29 AM

All replies

  • The filter function here is just filtering a set - the Same Store Flag isn't being applied to any query results. Try using the Same Store Flag in your WHERE clause instead, for example in a query like this:

    SELECT {MEASURES.MYMEASURE} ON 0,

    [store].[geograhical].MEMBERS ON 1

    FROM MYCUBE

    WHERE([store status].[same store flag].&[true])

     

    HTH,

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Thursday, April 8, 2010 12:25 PM
  • Hi Chris,

     

    Thanks for your kindly reply. In that case, I can't put the filter functio in where clause because the <Sales growth rate> of same store should be:

    Let say current month = 2010 / Apr

    Same store growth rate = Current month (2010/APR) Sales figure among {same stores in 2010/Apr} / Last Year same month (2009/APR) Sale figure among {same stores in 2010/Apr}

    Once I put the filter function in where clause, the equation become:-

    Same store growth rate = Current month (2010/APR) Sales figure among {same stores in 2010/Apr} / Last Year same month  (2009/APR)Sale figure among {same stores in 2009/Apr}

    Please kindly help.

     

    Cheers,

    Kevin Lam

    Friday, April 9, 2010 4:54 AM
  • Then you need to either create a new calculation, either in the query or in the cube where in the cube would probably be better, to present the measure for the ParallelPeriod. Have a look at the ParallelPeriod function in Books Online. If you need to do this for lots of measures and/or different "time versions" you can create a "Time Version" dimension in which you can add the fixed members like "Prior Period", "YTD" etc. You then define a calculation in your cube SCOPEd on the members on that dimension and apply the calculatio that is relevant. Sorry, can't really give an examle at the mo. but if I'm making sense I'm sure Chris or someone will take this further.
    Friday, April 9, 2010 7:13 AM
  • Can you post up the complete MDX for your original query and all related calculations please? I can see what you want to do, but it will be easier to explain the solution in terms of your own code.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    Friday, April 9, 2010 9:15 AM
  • Hi Chris,

    Here is my MDX:-

    WITH MEMBER [measures].[sales growth] AS
    (
    ([measures].[net sale])

    /
    ([Measures].[Last Year Net sale])
    ) - 1

    set [Same Stores]
    as
    'filter([Store].[Geographical].[storecode].members, [store period].[STORE STATUS].&[SAME STORE])'

    select

     ({[Same Stores]}, [Store].[Country].children, [measures].[sales growth])  on 0

    from
     [SUN WH - PnL2]
    WHERE
     [VW PERIOD].[YEAR - MONTH].[FIN_YEAR].&[2009].&[9]

    This MDX could retrieve a store-level <Sales Growth Rate> figure successfully. However, I would like to see it in the Country level <[Store].[Country]>. Do you know how to do that?

     

    Kevin

     

    Friday, April 9, 2010 10:54 AM
  • A subselect might be the easiest way of doing it. Does the following query do what you want?

    WITH MEMBER [measures].[sales growth] AS
    (
    ([measures].[net sale])

    /
    ([Measures].[Last Year Net sale])
    ) - 1

    select

     ([Store].[Country].children, [measures].[sales growth])  on 0

    from

    (SELECT

    NONEMPTY([Store].[Geographical].[storecode].members, ([measures].[net sale],[store period].[STORE STATUS].&[SAME STORE], [VW PERIOD].[YEAR - MONTH].[FIN_YEAR].&[2009].&[9])) ON 0 FROM
     [SUN WH - PnL2])
    WHERE
     [VW PERIOD].[YEAR - MONTH].[FIN_YEAR].&[2009].&[9]

    BTW, I've used the NonEmpty function instead of the Filter to find the Stores that have the Same Store status in September 2009 - hopefully it gives the same results, and it should be a little faster and more reliable than using Filter the way you were doing.

    Chris


    Blog: http://cwebbbi.spaces.live.com Consultancy: http://www.crossjoin.co.uk/
    • Marked as answer by Raymond-Lee Friday, April 16, 2010 2:50 AM
    Friday, April 9, 2010 11:29 AM