1. TARGIT Community
  2. Community
  3. General Discussion
Knowledge Base

Calculate with a dimension attribute on top of a hierarchy

Dirk Eveslage ·

I recently build a report in which I added a dimension attribute on top of a predefined hierarchy.

 

I am trying to sum up Quantity Base as a new measure here grouped by one Company. I only want to take specific Cost Centers from the second level of the underlying hierarchy. So for Company A the calculation should show 68.000 and for Company B 34.000. I can reference the Cost Center statically but the Company name will change.

I saw that there are calculations for predefined and custom hierarchies, but is there a solution when there is a mix of both? Or how can I attempt to calculate this?

1

Comments

3 comments
Sort by DateVotes
  • Niels Thomsen

    Hi Dirk

    I'm not  sure that I completely understand what you are trying to do - but as far as I can follow you, it should be possible.

    Levels are levels regardless of whether they come from predefined hierarchies only, custom hierarchies only or a mix.

    If you use the calculation rowlevel(), you will get a result telling you which level a certain row in you crosstable is on.

    You might also just go directly to the rowname calculation, which can search for a dimension value across all different levels, or you can add a paramater that specifies which level it should search.

    I've composed an  example in a table where you have a combination of Company attribute and a predeifned hiearchy:

    Check also the thorough explanation on the rowname calculation: https://community.targit.com/hc/en-us/articles/17792755367068-Rowname-and-Colname-calculations

    And also the rowlevel calculation: https://community.targit.com/hc/en-us/articles/18121531598236-Row-level-and-Column-level-calculations-rowlevel-and-collevel

    Hope it makes sense

    0
  • Dirk Eveslage

    Hi Niels thank you for answering,

    Yes that is part of what I want to achieve. The next step would be to sum the calculation "Only Gross Profit" by Company. 

    Meaning a measure which aggregates only by Company: sum(0, 0(l1), m2). In other words the Total of "Only Gross Profit" for the current company.

    In my example I calculated "Production Qty" = if colname("R&D PROD | R&D Production") then sum(0,0,m2) else 0 +  if colname("PROD | Production") then sum(0,0,m2) else 0. The next step would then be to sum "Production Qty" by the Companies. Because I want to divide the Amount of "SALES | Sales" with the quantites of Production. with sample data: Company A SALES [04100|packaging material] = 123 / 68.000

     

    0
  • Niels Thomsen

    ok - I think I get it now (and of course I should have said colname - not rowname :-)

    I think you will need some intermediate calculations - and the key will be that if you can "isolate" the numbers you need for your calculation, then you can adress them alone.

    There is no "Sumif" like calculation - therefore you need to first transport the numbers to another calculated measure and then do the calculation you really want.

    A hint to the Company totals could be setting up a way to distinguish between the companies (also an intermediate).

    Hope this gets you closer to a solution.

    See the example below:

    0
Please sign in to leave a comment.