1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer
Knowledge Base

Custom Hierarchy with a Baseline

John Jacobsen ·

In my analysis, I work with both a baseline and an ongoing change between the most recent statement and the previous statement at any given time. It is relatively easy to calculate the ongoing changes with the syntax sum (0, 0, m1) - sum (0, -3, m1, 0) - new measure. The problem emerges when I have to multiply the calculated changes by a baseline value in a custom hierarchy.

I have tried to make a another calculation (new measure) on the basis of this syntax: sum(0, d1, m3). I have tried with different levels ect. but without resolving the problem.

The blue markings in the calculated measure must be multiplied by the value marked blue. The same applies for the other colored selections. The values in "31-12-2019" is my Baseline!

Anyone knows how to fix this?

1

Comments

2 comments
Sort by DateVotes
  • Louise Stub Hansen

    Hi John

    Im not sure I understand your question correctly, but I try to answer anyway.

    If I understand you correct, you need to get the first three rows of "Gennemsnit pris" copied down in a new column. And then you can multiply it with your column "Mængde Ændring Hjælp".

    I would first do a new column that always count to 3 all the way down in the column (or more precise it counts 1, 2, 0, 1, 2, 0, 1, 2, 0 etc.). You can do this with the syntax: mod(allcount(d1, d1:0, m1);3)

    Then I would make a new column with your baseline using the syntax:

    if sum(c1, 0, m1) = 1 then sum(0, d1, m3) else if sum(c1, 0, m1) = 2 then sum(0, d2, m3) else if sum(c1, 0, m1) = 0 then sum(0, d3, m3) else 0 

    where (c1,0,m1) is refering to the column above.

    And the at last you can easy multiply the lastest column with your "Mængde Ændring Hjælp" directly.

    I hope it makes sence?

    See my example below:

     

    2
  • John Jacobsen

    Hi, Louise

    You understood it correctly. It works very nice.

    I had to make small changes to the syntax in order to get the result that I'm looking for.

    Thanks a lot for the help.

    1
Please sign in to leave a comment.