Rules - to sum up positive value of base elements only

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

    • Rules - to sum up positive value of base elements only

      Hello Everyone

      I have a cube called "TimeSheet" and it has the following dimensions:

      Period (this is the financial months Jan 2015, Feb 2015 etc)
      Company (UK, Germany etc)
      Department(IT, Finance etc)
      Location(London, New York etc)
      Week (Week 1, Week 2 etc)
      Staff Numbers
      Unbooked Hours (Delta between Actual hours booked by employee and Contractual Hours that they must book )
      Time (Normal time, Overtime)

      Week 1,2,3 etc are base elements, which then grouped into Months and then Year to date months and then finally grouped into Year. For example, Wk 5 to 8 is Month 2 (Feb), Year to date Feb would include total of Wk 1 to 8.

      If my weekly contractual hour is 40 hours per week, and I booked in my timesheet only 37 hours, my hours not booked will be 3 hours. However, if I book 42 hours, my hours overbooked will be 2 hours in negative. I want to ignore those overbooked hours. I only want to report Hours not booked (i.e positive delta). I don't know how to do that and I am completely stuck. Please HELP!! ?(

      Many thanks in advance
    • Didn't look in full detail, but if your problem comes down to cell XX only showing values >= 0, wouldn't it be enough to add a rule in this basic form:
      ['unbooked'] = IF(STET()<0,0,STET())
      ...or in plain text, "if non-rule value for 'unbooked' is smaller than 0, return 0, else return the non-rule value".

      Note that this construct "IF(STET()...) is only supported since version 5.1