Solved

If Statement Calculated Member MS Analysis Manager MDX

Posted on 2009-04-15
25
2,993 Views
Last Modified: 2013-11-16
I am trying to write a simple Iif statement for a cube in MS Analysis Manager. I want to check a level of a dimension and return measure values based on that criterion.

Here are a couple of attempts I have made. Although the syntax seems to pass, the results is always #ERR.

IIf( [Store_LWPA].[Opening Year Month].Dimension = 0   ,0,1)

IIf( [Store_LWPA].CurrentMember.[Opening Year Month] = 0   ,0,1)

I am brand new to this. Anyone have some direction for me?
IIf( [Store_LWPA].[Opening Year Month].Dimension = 0   ,0,1)
 

IIf( [Store_LWPA].CurrentMember.[Opening Year Month] = 0   ,0,1)

Open in new window

0
Comment
Question by:btrieve
  • 12
  • 7
  • 6
25 Comments
 
LVL 5

Expert Comment

by:garycris
ID: 24148027
Try using IsGeneration() or IsLeaf()
IIf( IsGeneration([Store_LWPA].CurrentMember, 0) ,0,1)
 

OR
 

IIF(IsLeaf([Store_LWPA].CurrentMember) ,0,1)

Open in new window

0
 

Author Comment

by:btrieve
ID: 24148320
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
0
 
LVL 5

Expert Comment

by:garycris
ID: 24148749
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?

0
 

Author Comment

by:btrieve
ID: 24149376
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
0
 
LVL 5

Expert Comment

by:garycris
ID: 24149967
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)
0
 
LVL 6

Expert Comment

by:agandau
ID: 24150432
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]

Open in new window

mdx-output-sample.JPG
0
 
LVL 6

Expert Comment

by:agandau
ID: 24150475
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.
 
0
 

Author Comment

by:btrieve
ID: 24150899
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.
0
 

Author Comment

by:btrieve
ID: 24151199
Guys,

Can none of this be done with a case statement?

Ryan
0
 
LVL 5

Expert Comment

by:garycris
ID: 24151460
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.
0
 

Author Comment

by:btrieve
ID: 24151533
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
0
 
LVL 6

Expert Comment

by:agandau
ID: 24151705
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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 6

Expert Comment

by:agandau
ID: 24151731
Also out of curiosity, could the case statement read something like

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

Author Comment

by:btrieve
ID: 24151853
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
0
 
LVL 5

Expert Comment

by:garycris
ID: 24152040
"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
0
 

Author Comment

by:btrieve
ID: 24152223
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
0
 
LVL 6

Expert Comment

by:agandau
ID: 24152251
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)

0
 
LVL 5

Expert Comment

by:garycris
ID: 24152311
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
0
 

Author Comment

by:btrieve
ID: 24157056
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?


0
 

Author Comment

by:btrieve
ID: 24167343
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?
0
 
LVL 6

Expert Comment

by:agandau
ID: 24169654
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?
0
 

Author Comment

by:btrieve
ID: 24170347
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?
0
 
LVL 6

Accepted Solution

by:
agandau earned 500 total points
ID: 24171143
I know a case statement is what you'd like but what you're looking for wouldn't really be a case statement in MDX.  Instead your measure will be constructed from a tuple.  I wish I had access to my Adventureworks analysis database to cook up an example for you right now.

Here's some info on getting the current date into an expression (written by Mosha Pasumanski, one of the founding fathers of the MDX language):

http://www.sqljunkies.com/WebLog/mosha/archive/2007/05/22/current_date_mdx.aspx

I hope the following doesn't sound like a lecturing tone, but you did say you're new to MDX:  In case you're not familiar with what a tuple is, in the code it's a parentheses wrapped, comma delimited list of members from every dimension in the cube.  What it returns is a measure value residing at the cross section in the cube of those members.  The confusing thing at first is that most tuples are not written as though they contain a member from every dimension, because in the code, if a dimension isn't represented, then the CurrentMember for that dimension is assumed.

All I think you're looking for is a measures defined as tuples:
TYSales = ([Time].[All].[2009], [Measures].[Sales Dollars])
LYSales = ([Time].[All].[2009].PrevMember, [Measures].[Sales Dollars])

Now the trick is to get rid of the explicit reference to 2009 removed, and I think the article in the link above can help you out with that.

0
 

Author Comment

by:btrieve
ID: 24184774
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?
0
 

Author Closing Comment

by:btrieve
ID: 31570441
Thank you, I have it working. You are a genius!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now