Strings comparison with GE, GT,.. for CurrentMonth

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Strings comparison with GE, GT,.. for CurrentMonth

      Hi all,
      I could not get out of a problem I have when trying to dynamically set a value depending on the month, compared to "CurrentMonth".

      Here's the situation:
      I was able to get the strings for
      - the Month referenced by the palo.datac formula: using Palo.Ename and Palo.Eindex I can get e.g. 200903 as for March 2009
      - the current month: using dateformat and NOW(), I can get 200904 as for April 2009

      Now, it seems that using GE or GT to compare those strings does not work properly in my case, I don't know if it's a bug or I'm missing something. While the actual rule was more complicated, you find below an example to reproduce the behaviour in the Demo db:

      1. in the Datatypes dimension, create the following members:
      - Example (numeric)
      - PaloEname (string)
      - DateFormat (string)
      The second and third are only for check purposes, see below

      2. In the Sales cube, add this rules:

      ['Example'] = IF(ge(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')),DATEFORMAT(NOW(),"\Y")),['Budget'],['Actual'])

      the above is the important rue, the following ones are for check

      ['PaloEname'] = PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years'))
      ['DateFormat'] = DATEFORMAT(NOW(),"\Y")

      So, according to first rule, I should get Budget if the month is in the future, and Actual if it is in the past, but now paste a view like the attached one (I manually entered values in Actual and Budget since my db was empty): you can see that, while the members PaloEname e DateFormat are properly filled, "Example" always get the same value (Actual). Practically the second element in the comparison is always evaluated greater than the first. X(

      I also tried to compare using value(..) instead of comparing the strings but it worked even worse.

      Any suggestion?

      Thanks in advance,

    • RE: anyone from Palo on this

      did you try

      ['Example'] = IF(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years'))==DATEFORMAT(NOW(),"\Y"),['Actual'],['Budget'])

      intead of

      ['Example'] = IF(ge(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')),DATEFORMAT(NOW(),"\Y")),['Budget'],['Actual'])


      Or how about

      ['Example'] = IF(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years'))==STR(DATEFORMAT(NOW(),"\Y"),4,0),['Actual'],['Budget'])

      Or maybe >= operator works...didn't test it so far

    • still not working

      Hi Holger,

      thanks for your suggestions, unfortunately both don't work for me:
      - the first always returns the value in 'Budget'
      - the second (modified as follows in order to eliminate the "str wrong data type" error):
      ['Example'] = IF(PALO.ENAME("Demo","Years",PALO.EINDEX("Demo","Years",!'Years')) == STR(VALUE(DATEFORMAT(NOW(),"\Y")),4,0),['Actual'],['Budget'])
      returns 'Budget' value as equal.

      I also tried with >=, GT, LT, etc, but either I always got 'Budget' value or I always got 'Actual' values, I'm not able to get a different value depending on the Year I'm refereincg.

      Any further help will be highly appreciated,

    • RE: still not working

      How about using


      instead of

      PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')

      I used to use the second expression too but the first one is much more simple and also works....and maybe brings the solution for your issue?


      P.S: I can see that you checked it on consolidated level. Does it behave the same way on base level e.g. Italy, Jan, Desktop L?

      ====>Another Edit:

      You now what. Think I got it. When comparing with >= GT, e.g.
      we NEED numeric stuff!!!!!!!! so it should be

      ['Example'] = IF(ge(VALUE(!'Years'),VALUE(DATEFORMAT(NOW(),"\Y"))),['Budget'],['Actual'])

      Please tell me it works. Otherwise I am out of ideas for now :)

      The post was edited 3 times, last by h_decker ().

    • Finally I did it! Don't ask me what's the difference with the first version, but now the calculations are properly handled... here's the solution:

      ['Example'] = IF(ge(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')),DATEFORMAT(NOW(),"\Y")),['Budget'],['Actual'])

      STR(!'Years',4,0) does not work for me (he says "wrong data type for the STR function).
      Actually I initially tried to compare VALUES (even if GE should work with strings too, according to the manual...) instead of strings but VALUE(DATEFORMAT(NOW(),"\Y") used to return 0... Now also this returns the proper value so also this solution would work.

      In a sentence, a strange false problem that made me (and you) loose some time, leaving me someway puzzled about the reliability of the rules engine...

      Thanks again, regards,

    • Hi,
      good to hear it works now but

      very strange :) Indeed I can see no difference between
      ['Example'] = IF(ge(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')),DATEFORMAT(NOW(),"\Y")),['Budget'],['Actual'])


      ['Example'] = IF(ge(PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')),DATEFORMAT(NOW(),"\Y")),['Budget'],['Actual'])


      Try substitue PALO.ENAME("Demo","Years",PALO.EINDEX ("Demo","Years",!'Years')

      This should work and is a little shorter.

      May it was a rule cache issue and it just worked after restarting the service.

      Have a nice weekend.

    • not sure what is happening

      Very strange indeed, the very same rule is no longer working at this moment (and value(dateformat(....)) now returns 0 again). I can't figure out what's happening... I'll update the post when I catch some interesting info...

      Update: I found that a more general problem occurs when handling comparisons... I posted a new more general thread under this section.

      Update as of 20 apr 2009: it seems that a more general problem exists while using comparisons among Measures, please see the following thread:

      It was recognized as a bug.

      The post was edited 3 times, last by realquo ().