If Statement Calculated Member MS Analysis Manager MDX

Here is what I want to do. If a level of my store dimension is >= a certain value, I want to bring back the sales dollars for that store/item combination.

For example, if [Store_LWPA]...[Open Year Month] >= 200701, bring back sales for all items sold at that store.

Then, in a second calculated member, I will need to do the same evaluation, but bring back comparable sales for that store/item from the previous year.

I am well versed in SQL but have never written an MDX query in my life. Any ideas, resources?

Thanks,

Ryan

I need to understand your hierachy a little better.  Is 200701 a member in you hierarchy or is a value that you are loading?

Store_LWPA is your dimension, correct?
Is [Open Year Month] part of the Store_LWPA dimension or is it a member of a different dimension like Measures?

I'm thinking 200701 is a value loaded to the measure [Open Year Month] for different stores in the [Store_LWPA] dimension.  Am I correct?

Hi,

Thank you so much for your responses.

The dimension I have is Store_LWPA with levels [Store Code]...[Store Desc]...[Opening Year Month].

I added Opening Year Month simply so that I can use that in my formula. The value for Opening Year Month is assigned in SQL when I update the Dimension Table for Store_LWPA.

Thanks again for your help.

RMc

I'm not sure if this is going to be the best way to do it.  You have set it up that 200701 is a member, therefore you are going to be limited in the way you evaluate it.  You can't say IF member is greater than or equal to another member and get the result you are looking for.  It would be kind of like saying IF member >= blue.  So blue evaluates but what is red?  Is red greater than or less than blue?

In Essbase, we have a concept of attributes, which can be text or numeric, I'm not sure in Analysis Services if there is something similar.  The way I would look at it is more along the road I was going down before where you set up [Open Year Month] as a measure and add it to your fact table.  Then you can evaluate the measure [Open Year Month] for any store and do your >= test.

If you just wanted to test for = then you could use the IS function
IIF(IS([Store_LWPA].CurrentMember, [200701]), 0,1)

I can't think of a way to evaluate the member for greater than, because as I mentioned before, it's really a text name and MDX does not support a CAST type function like in SQL where you could convert it to an integer for the evaluation.

I would make it a measure and then do something like
IIF( ([Store_LWPA], [Opening Year Month]).value >= 200701  ,0,1)

btrieve, could you whip up a sample output of the query you expect, along with whatever parameters the user entered at the time?  Nothing fancy, even just some text is good, along with some explanation of the user set parameters.

I agree with garycris that using the opening year month this way may not be most suitable.

I remember the biggest problem I had with learning MDX was trying to make it behave like SQL.  In SQL a where clause to filter out rows, because the select clause only defines what becomes the column headers.

So in MDX, you have to ask yourself, "what is the set of things I want on my row headers, and what is the set of things I want on my column headers".  Forget about filtering at first.  Maybe you're already well past this point with learning MDX (took me a while!).

In SQL you'd think of filtering rows where the yearmonth >= 200701, but in MDX you filter the rows by generating the set of all year months beginning with 200701 and ending with <some month in the future>.  The problem here is that dates won't really behave like dates unless they are stored in a time dimension, while this opening year month is an attribute of the Store Dimension.  

If I understand what you're trying to do correctly, I would generate a set of stores with the following code.  Note that the picture and the code have nothing to do with one another.

SELECT
 
   {[probably some measures]}
ON COLUMNS,
 
   FILTER(
      [Store_LWPA].Members, -- start with the set of all stores
      SUM(
         [Date].[All].[2007].[2007 Q1].[2007 01]      -- begin of range
         :                                            -- range operator
         [Date].[All].LastChild.LastChild.LastChild   -- end of range
      ) > 0.0
   )
ON ROWS
 
FROM
   [MyCube]
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

Again, the code above and the picture are unrelated.  I left out a point I meant to make, that the filter is basically saying: give me the set of stores that have sales in 200701 or beyond.

This prompts me to ask, why do you need the opening year month?  Would the fact that store doesn't have sales before it opens be enough?  I guess now that I think about it my suggestion above relies on that notion.

Anyway, keep the questions or clarifications coming.

Hope this helps.
 

Thanks to both of you for these enriching responses.

The reason for this logic is that retail stores have to have been open for a minimum of 12 months to qualify as "comparable stores". This is to prevent newly opened stores from contributing unduly to this year's sales, which would lead to an inaccurate comparison with last year, when fewer stores existed.

My goal is to actually create two new columns, TYCompSales and LYCompSales. In intend to do this by evaluating the store's Opening Year Month with a critical value.

For now I intend to attempt Gary's suggestion. The problem is that I have never used MDX really, so I expect quite a challenge. Thanks again for your helpful comments.

Guys,

Can none of this be done with a case statement?

Ryan

A CASE statement is the same as an IIF statement.  Albeit a lot cleaner, and my prefered method, but I still don't see how you do the comparison, whether it is an IIF statement or CASE.

Well, now I took your suggestion and put Opening Year Month as a measure in my fact table.

Couldn't I have a case like:

CASE [Measures].[Opening Year Month]
         WHEN [Measures].[Opening Year Month] > 200701 THEN [Measures].[DollarsSold]
 
         ELSE 0
end

If you mean to put that case statement in your SQL query, then yes.  You would then have a measure displaying only sales qualified for comparison, and you avoid having to complicate any MDX.  You'd simply pull the desired measure in your MDX query.

On the other hand (this is my preferred way) lets say you have several measures that require the same filtering.  One thing you can do, to avoid having to duplicate all those measures would be to take your case statement and have it return some pre-set fixed switched value:

CASE WHEN [Measures].[Opening Year Month] > 200701 THEN
    1
ELSE
    0
END as CompSalesFilterKey


Then create a small named query in your DSV and create a dimension from it.

SELECT 0 AS CompSalesFilterKey, 'Store First Year Sales'
UNION
SELECT 1 AS CompSalesFilterKey, 'Comparable Sales'

All your queries will have the option to filter any measure depending on whether the transaction happened in the first year of sales.  By default (unless you tinker with the all level of the dimension) all sales would show up, but you could filter out the first year sales by putting something like this in the where clause:

WHERE ([Comparable Sales Filter].[&1])  -- or whatever key value you choose.

Also out of curiosity, could the case statement read something like

CASE WHEN DATEDIFF("mm", Store Open Date, Sale Date) > 12

Hi,

Well, I would love to have solved all this in SQL, but my boss is opposed to it, and actually there is good reason for her opposition.

The amount of data is many millions  of records. We get the newest day's data at 7:30 in the  morning and we need the information available ASAP after that. To update two columns in SQL for multi-million rows would be quite time consuming. Also, because the comp store filter is based on the current moment in time, it would need to be recalculated at least weekly, possibly daily.

To your other question, the datediff will not work because of special logic the retail chain uses for there comps that I must replicate.

Ryan

"Couldn't I have a case like:

CASE [Measures].[Opening Year Month]
         WHEN [Measures].[Opening Year Month] > 200701 THEN [Measures].[DollarsSold]
 
         ELSE 0
end"

Yes that is what I was thinking.
Not sure if you will have a syntax issue with the way you wrote it, I would write it as

CASE WHEN [Measures].[Opening Year Month] > 200701 THEN [Measures].[DollarsSold]
ELSE 0 END

Hi Gary,

Thanks for your suggestion about the case. Unfortunately your syntax does not work for me. It is telling me "token not valid"

RMc

That helps out a great deal, I can see why you'd want to attach the logic to the store rather than to the millions of rows.

Here's another thought, you could create add the Store Dimension a second time in the DSV and then use it as an additional fact table.  Add this table as the source for a new measure group, and then add only one dimension (the store dimension) and one measure (the numeric value of the opening year month).

The measure group should process pretty quickly (I'm guessing there aren't millions of stores) and it sounds like the all the data you need you've already added to the database.

Getting back to your original request, then the IIF statement in the calculated member would be:
   IIF(([Store_LWPA].CurrentMember, [Measures].[Open Year Month]) = 0, 0, 1)

agandu jogged my memory, you can't just hit the measure, you have to hit the tuple, and you will probably need a value function

CASE WHEN ([Store_LWPA].CurrentMember, [Measures].[Opening Year Month]).value > 200701 THEN [Measures].[DollarsSold]
ELSE 0 END

Hi Guys,

Unfortunately that last syntax didn't work for me either, Gary.

Also, how do I write two case statements that will give me two new columns, TYSales and LYSales? Can this be done if the "time" dimension is not technically a time dimension but rather a standard dimension with time components?

Hi Guys,

I have the time dimension set up properly thanks to agandau. I was also able to get my "comparable stores" flag to work using a dimension table.

All I need now is the case statement to separate out the columns for TYSales and LYSales.

Any advice?

I'm lost - let's back up a bit here.  Lots of suggestions have been made and I'm not clear what you've already tried.

Can you provide a small sample of the data behind the store dimension?

Sure, but I really just need a case statement that will say

Case when Year = year(getdate()) then [measures].[Dollars Sold] else null

for TY Sales

and

Case when Year (time dimension) = year(getdate())-1 then [measures].[Dollars Sold] else null

I am mixing in SQL because I am lost with the MDX.

Can you help or do you need more information?

Hey,

I really like your comments. I think this is the solution but I am getting a syntax error.

I am inserting your statement into a calculated member in Analysis Manager and it tells me it cannot find the dimension member in a name binding function. What does this mean?