MDX iif date greather than doesn't evaluate correctly in my cube

Question

I have a problem which seems to be very simple to solve but I can't. In my Fact table I have a Timestamp field which is a smalldatetime Type. This fact is linked to a Time dimension via its fulldate_Fk (also SmallDatetime). So What I would like to have is to compare the timestamp with the FullDate_FK from the fact to create a calculation like this:

iif([Dim Time].[Date].CurrentMember.MemberValue <= 
    [Fact].[Timestamp].CurrentMember.MemberValue
   ,[measures].[YTD Actuals]  
   ,[measures].[YTD Actuals]+[measures].[YTD Com])

But it is not working at all. All [Dim Time].[Date] seem to be evaluated as < than the Timestamp. P.S: The Timestamp is the last date when the data have been loaded in the DB (in my case 31/08)

Here the result I got:

 MONTH   |  YTD Actuals  |  YTD Com   |  Calculation;
 JAN  ,        10      ,        10   ,        10;
 FEB  ,        20      ,        10   ,        20;
 MAR  ,        40      ,        20   ,        40;
 MAY  ,        60      ,        30   ,        60;
 JUN  ,        70      ,        50   ,        70;
 JUL  ,        85      ,        50   ,        85;
 AUG  ,       120      ,        55   ,       120;
 SEP  ,       120      ,        60   ,       120;
 OCT  ,       120      ,        70   ,       120;
 NOV  ,       120      ,        80   ,       120;
 DEC  ,       120      ,        90   ,       120;

From August, I should have the sum of Actuals YTD and Com YTD in the calculation, but I still have the Actuals YTD only?

Extra Info I'm using PivotTable just by dragging attributes in Excel. Month in rows and measures (the 2 YTD and the new calculated member)

Thanks for your comment SouravA! I'm just using PivotTable just by dragging attributes in Excel. Month in rows and measures (the 2 YTD and the new calculated member). — BeeIdev
From which dimension are you dragging the "month" attribute? Does your fact and Dim Time dimension have the month attribute? — SouravA
Just my Dim Time have a month attribute. My fact has a TimeStamp date attribute and a Date_FK attributes which refers to the date in the Dim time attribute. These are the two dates I want to compare... — BeeIdev
Have you confirmed that your data types are set correctly and it isn't somehow comparing them as strings which have the date formats written differently? — mmarie

GregGalloway GregGalloway · Accepted Answer · 2015-10-06T04:44:07

If you build a new calc which is:

[Fact].[Timestamp].CurrentMember.MemberValue

What does it return when you add it to your PivotTable? Null? I suspect the CurrentMember is the All member so MemberValue is null. But let's test that.

Do all rows in the fact table have the same Timestamp or are there many different timestamps?

If your fact table has 10000 rows are you expecting the IIf calc will be evaluated 10000 times (once for each row)? That's not the way MDX works. In your PivotTable that has 12 rows the IIf calc gets evaluated 12 times at the month grain.

If you want the calculation to happen on each of the 10000 rows then write the calculation in SQL and do it in a SQL view before it gets to the cube.

To make the calc work as you intend in the cube consider doing the following. Add a new column in your DimTime SQL table called Today Flag. It should be updated during the ETL to be Y only on the row which is today and should be N on other rows. Then add that column as a new attribute to your Dim Time dimension. You can make it Visible=False. Then go to the Calculations tab and flip to the Script view and replace your current [Measures].[Calculation] calc with this:

Create Member CurrentCube.[Measures].[Calculation] as
[measures].[YTD Actuals];
Scope({Exists([Dim Time].[Month].[Month].Members,[Dim Time].[Today Flag].&[Y]).Item(0).Item(0):null});
    [Measures].[Calculation] = [measures].[YTD Actuals]+[measures].[YTD Com];
End Scope;

MDX iif date greather than doesn't evaluate correctly in my cube

1 Answers