CODEWITHSUNDEEP
mdx
RunW
RunW

Reputation: 269

How to make a grouping dimension using mdx

I'm trying to create a bucketing grouping using mdx. I have a measure called QtyOpen and a Time dimension.

I would like to have a dimension with this logic:

Bucket 1 for showing all OpenQty up until yesterdays date

Bucket 2 for showing all OpenQty for todays date

Bucket 3 for showing all OpenQty for tomorrows date

So if all OpenQty until yesterday is 15, all OpenQty for today is 7 and all OpenQty for tomorrow is 12, it should display like this:

Bucket 1 | Todays date | Tomorrows date
   15           7              12

So I have managed to get the syntax for the date range that I want. The query for that looks something like this:

select (
            {StrToMember("[Time].[Time YQMD].[Year].&[" + format(now(), "yyyy") + "].&[Q" + format(datepart("q", now())) + "].&[" + format(now(), "MMM") + "].&[" + format(now(), "dd") + "]")
            : 
            StrToMember("[Time].[Time YQMD].[Year].&[" + format(now(), "yyyy") + "].&[Q" + format(datepart("q", now())) + "].&[" + format(now(), "MMM") + "].&[" + format(now(), "dd") + "].lead(3)")}
            ) on columns, 
non empty ([PPV].[Country].[Country], [Measures].Receipt Quantity]) on rows
from [Model]

What's missing here is "Bucket 1" which collects the OpenQty for all days previous to today.

I would be a great bonus to be able to use this dimension over all kind of measures.

Thanks.

Appreciate any help.

Thank you.

Upvotes: 1

Views: 203

Answers (2)

whytheq
whytheq

Reputation: 35557

You can do this in a WITH clause:

WITH
  SET [Today] AS
    STRTOSET(
       "{[Time].[Time YQMD].[Year].&[" + format(now(), "yyyy") + "]" + 
       ".&[Q" + format(datepart("q", now())) + "]" &
          ".&[" + format(now(), "MMM") + "].&[" + format(now(), "dd") + "]}"
    )
  MEMBER [Time].[Time YQMD].[All].[Today] AS
    [Today].item(0).item(0)
  SET [Pre-Today] AS
    {null:[Today].lag(1)}
  MEMBER [Time].[Time YQMD].[All].[Pre-Today] AS  
    AGGREGATE([Pre-Today])
  MEMBER [Time].[Time YQMD].[All].[Tomorrow] AS
    [Today].item(0).lead(1)
  SET [BUCKETS] AS
   {
     [Time].[Time YQMD].[All].[Pre-Today]
    ,[Time].[Time YQMD].[All].[Today]
    ,[Time].[Time YQMD].[All].[Tomorrow]
   }
SELECT
   [BUCKETS] ON 0, 
NON EMPTY 
  ([PPV].[Country].[Country], [Measures].Receipt Quantity]) ON 1
FROM [Model];

Upvotes: 0

BICube
BICube

Reputation: 4681

What you need is called Dynamic Time Calculations. Easy to set up but not very straightforward. See this link it will help you out: Dynamic Time Calculations

Upvotes: 1

Related Questions