Sum of Cells via Attribute

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

    • Sum of Cells via Attribute

      Hi,

      I am trying to solve a Problem, where I want to sum up cells with the specified attribute.

      Two Dimensions have a attribute to connect these.
      In the first Cube stand some input values and I want to sum up them for the second cube via attribute.

      Like:
      Sum (All Cells with Attribut == X)

      Is this possible?
      If not I think I have to create a cube which has both dimensions and use this as "intermediate calculation".

      edit:
      Tried the soultion with a new cube.
      This "works", but now my core crashes...
      I deleted some rules and cubes, but it still crashes.

      Should now look what happend :D


      Details:
      One of 2 Rules in the new Cube (i could delte the old one)

      Source Code

      1. ['Kennzahlen':'Prozessmenge'] = IF(OR((!'Prozesse') == "Alle Prozesse", PALO.DATA("versicherung_planung", "#_Prozesse", "Abteilung_ID", !'Prozesse') != PALO.DATA("versicherung_planung", "#_Abteilung", "Abteilung_ID", !'Abteilung')), "Keine Aggregation", PALO.DATA("versicherung_planung", "Mengenplanung", !'Jahr', "Prozessmenge", !'BudgetVersion', PALO.ENAME("versicherung_planung", "Kostentreiber", VALUE(PALO.DATA("versicherung_planung", "#_Prozesse", "Kostentreiber_ID", !'Prozesse')))))

      What is the rule doing: Check if its not the top element and if the attributes are equal. If the condition is false it reads the value of another cube.

      I think this one is too complex?
      There are many operations.
      It looks like I have to find another way for this:

      Cube 1: Plans for 9 Elements values
      Cube 2: Should read the values and write them to the matching attribut ids (This Dimension in Cube 2 hast 300 Elements) Then I calculate with this value.

      The post was edited 2 times, last by naitsabes ().

    • Hi,
      best way if you want to calculate sum over some element hierarchy is to use rules to filter values on elements with the right attribute value
      ['attribX'] = B: if (palo.data("db","#attrcube","attrname",!'dimension')=="X",['value'],0)
      you just have to create new element in the dimension containing the value you want to sum (in my example it is element 'value')
      and then you can query any consolidated element in other dimension.
      If the calculation is slow because of large dimensions, you can define marker on value
      ['attribX'] = B: if (palo.data("db","#attrcube","attrname",!'dimension')=="X",[['value']],0)
      The core is not crashing because rules. Rules are processed in OLAP

      regards

      Jiri
    • Thanks for the fast answer.

      Will try Markers and the first solution (maybe more rules, but if it works).

      This Means:

      For each element in the dimensions I have to create an element in the other dimensions which consolidates the elements I want to sum ?
      Then it would be "easy" to sum with the rule.

      Okay but there is a relation between crashing and rules?
      Cause if I deactivate them, there is no problem.

      edit: For information the crash was in the new cube, where I want to consolidate two dimensions from the first cube.

      Now I will try the other solution :)

      The post was edited 1 time, last by naitsabes ().

    • You shouldn't edit old posts so significantly. Better to add new one. This way is to hard to follow what's going on in the thread.
      The rule you added to the first post looks ok. Just make sure the part:
      PALO.DATA(
      "versicherung_planung",
      "#_Prozesse",
      "Kostentreiber_ID",
      !'Prozesse'
      )
      returns always number >= 1
      because PALO.ENAME expects numeric param. I'd personally store element name instead of element position in attribute "Kostentreiber_ID" (e.g. Kostentreiber_Name)
      and write
      PALO.DATA(
      "versicherung_planung",
      "Mengenplanung",
      !'Jahr',
      "Prozessmenge",
      !'BudgetVersion',
      PALO.DATA(
      "versicherung_planung",
      "#_Prozesse",
      "Kostentreiber_Name",
      !'Prozesse'
      )
      )
      What process is crashing Palo server or "core" process (spreadsheet server)?
      BTW what version are you using Jedox or Palo CE?
      If Jedox (Base), you should report the bug. If Palo CE I think it will never be fixed

      Jiri