1

Hoping for any help with hopefully a simple problem - I'm writing MDX into a rule builder within a company application. Here is the expression:

IIF(Time.CurrentMember >= [Time].&[456], "Formula 1" , "Formula 2")

The expression should compare each date in the report to [Time].&[456], which is November 2022 -- if the date is after or greater than or equal to [Time].&[456], it should return Formula 1. If it is before or less than [Time].&[456], it should return Formula 2.

When I enter this expression into the application, I see Formula 2 throughout except for November 2022 where Formula 1 is returned. The expression seems to interpret the "=" sign correctly but doesn't interpret any other operator sign.

Is there a way to update this expression so dates can be compared correctly?

I also added MemberValue to the expression--

IIF(Time.CurrentMember.MemberValue >= [Time].&[456].MemberValue, "Formula 1" , "Formula 2")

But this also did not evaluate correctly - Formula 1 and Formula 2 were returned randomly throughout the report.

Any help would be appreciated! Thank you.

burnsi
  • 6,194
  • 13
  • 17
  • 27
fashpixie
  • 11
  • 1

1 Answers1

0

I think that your problem is that MemberValue 'November 2022' is not actually a date, but just a string, that gets compared with, for example, 'January 2022', or 'February 2022'. You should probably change your Time dimension, and compare only on leaf level (for example you cannot compare Q4 2022 and Nov 2022..)). But if your Member Value is, for example, 2022-11-31 you should be able to compare properly..

vldmrrdjcc
  • 2,082
  • 5
  • 22
  • 41
  • That makes sense - thank you for your response. I think I might be limited by the application's rule builder and might not be able to change the actual Time dimension. I'm thinking what I'm attempting to do is not possible within the rule builder. – fashpixie Jan 20 '23 at 22:28