Using attribute and rules to apply certain multiplier to a measure

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

    • Using attribute and rules to apply certain multiplier to a measure

      Hello, I'll try to explain my problem in the clearest possible way...
      I'm working with a cube with this dimensions:

      - Products (desktop,monitor,...)
      - Months
      - Movements (actual, budget, including taxes)
      - Tax (Notax, 20%, 10%, 8%)

      In the Tax dimension I added an attribute "value" and then I've filled a view with #_Tax so that
      Notax has attribute = 1;
      20% has attribute value = 1,20;
      10% has attribute value = 1,10;
      8% has attribute value = 1,08;

      I want to do this in order to create movements with taxation... I.E.
      When I, for instance, add a movement (actual) for Product: "Desktop L", I wish I could select which kind of taxation will be on that movement (for example 20%)
      and then calculate automatically the "including taxes" value on dimension Movements.

      For example I add:

      Movements-actual: 100
      Months: Gen
      Tax: 20%

      I want that
      Movements-including taxes: 120
      Months: Gen

      I think I need a rule to calculate this "including taxes" but I don't know how I can select the right elements in the Tax dimension...


      Thank you very much for your help!

      Regards
    • I've created this rules:

      ['including taxes'] = ['actual'] * PALO.DATA("zio","#_Tax","Value",!'Tax')


      and now if I select Tax 20% for a movement of 1000 I got the including taxes column: 1200. Right!

      My problem now is that I wish I could see every movements including taxes without consolidating them. For example

      Movements-actual: 1000
      Tax: 20%
      Movements-including taxes: 1200;


      Movements-actual: 1000
      Tax: 10%
      Movements-including taxes: 1100;



      Right...

      What if I wanna see all movements? I tried to modify the Tax dimension so that I have a No tax and then a "Taxed" root with 20%, 10% and 8% as child.

      BUT, if I select the "Taxed" root the consolidation work bad for my numbers, I wish I see this:

      Product: Desktop L
      Tax: 20%
      Movement-actual: 1000
      Movement-including taxes: 1200


      Product: Desktop Pro
      Tax: 10%
      Movement-actual: 1000
      Movement-including taxes: 1100


      All desktop consolidation I expect:
      Movement-actual: 1000+1000 = 2000
      Movement-including taxes: 1200+1100 = 2300

      But instead I find:
      Movement-actual: 1000+1000 = 2000; OK.
      Movement-including taxes: 1200+1100+1080 = 3380

      Even if no movements with tax at 8% were inserted...

      How can I avoid this behavior ??

      Thanks really much!
    • I should apply this rule ONLY to base elements... So I changed the rule as follows:

      ['including taxes'] = N:['actual'] * PALO.DATA("zio","#_Tax","Value",!'Tax')

      And now the behavior is what I was expecting...

      But my question still remain: is this the right way to do such a job or I'm doing something wrong or just too complex... ?

      Thanks
    • Hi Patrick,

      Firstly, well done for working this out and thanks for sharing.

      Where the value is a constant i.e. your tax level of 20% will always have a value of 20% then this will be fine.

      Using attributes means the value is applied globally so any change you make can have a forward and retrospective impact on rule calculated values. This becomes more of an issue where the attribute may be more generic in nature i.e. Tax Rate or where an attribute may change over time.

      Hope this helps.

      Rod