1. TARGIT Community
  2. Community
  3. User Tips and Tricks

Calculating Average for TOP / FLOP and Middle Area of Crosstab

Avatar
Marc Hoeller

Hi Experts,

I'm just working on a very basic requirement that sound simple, but not easy to solve.

Maybe someone has a great idea :-)

We have a list with Values, for example 20 values with a measure.

Now we want to get the Average of the TOP (X) Percent, The FLOP (X) Percent and

… and this is what makes it tricky:

The Average of the „middle“ Section, means the Values lower the TOP (X)% and higher as the FLOP (X)%.

So I want to receive 3 Measures:
- Average TOP (X) %
- Average Middle (for example Area between TOP 20 and FLOP 30) %
- Average FLOP (X) %

 

I made an example in Excel:

0

Comments

2 comments
Sort by Date Votes
  • Avatar
    Ole Dyring
    (Edited )

    Hi Marc,

    Not the easiest one to implement. Not even sure I understood all your requirements. Anyway, here is my take at it:

    To achieve the above, you will need to add a LOT of intermediate calculations.

    Here is what the table will look like, before hiding all the intermediate calculations (all added as calculated columns):

    The calculations I did:

    • c1: if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 0,5 then 1 else if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 0,8 then 2 else if sum(d1, d1:0, m1) / sum(d1, all, m1) <= 1 then 3 else 0
    • c2: if sum(c1, 0, m1) = 1 then sum(0, -1, 0, 0) + 1 else 0
    • c3: if sum(c1, 0, m1) = 1 then sum(d1, 0, m1) else 0
    • c4: if sum(c1, 0, m1) = 2 then sum(0, -1, 0, 0) + 1 else 0
    • c5: if sum(c1, 0, m1) = 2 then sum(d1, 0, m1) else 0
    • c6: if sum(c1, 0, m1) = 3 then sum(0, -1, 0, 0) + 1 else 0
    • c7: if sum(c1, 0, m1) = 3 then sum(d1, 0, m1) else 0
    • c8: sum(c3, all, m1) / max(c2, all, m1)
    • c9: sum(c5, all, m1) / max(c4, all, m1)
    • c10: sum(c7, all, m1) / max(c6, all, m1)
    • c11: if sum(c1, 0, m1) = 1 then sum(c8, 0, m1) else if sum(c1, 0, m1) = 2 then sum(c9, 0, m1) else if sum(c1, 0, m1) = 3 then sum(c10, 0, m1) else 0
    • c12: if sum(c1, 0, m1) = 1 then 0 else if sum(c1, 0, m1) = 2 then sum(c11, 0, m1) - sum(c8, 0, m1) else if sum(c1, 0, m1) = 3 then sum(c11, 0, m1) - sum(c9, 0, m1) else 0

    Note: If you want your groups to exceed the threshold values (0,5 and 0,8 in above example) before shifting to the next level, you may want to change your c1 calculation to this:

    • c1: if sum(d1, d1:-1, m1) / sum(d1, all, m1) <= 0,5 then 1 else if sum(d1, d1:-1, m1) / sum(d1, all, m1) <= 0,8 then 2 else if sum(d1, d1:-1, m1) / sum(d1, all, m1) <= 1 then 3 else 0

    BR / Ole

     

    0
  • Avatar
    Marc Hoeller

    Hi Ole,

    WOW, thanks a lot for your efforts to help in this requirement.

    First, I will analyse your formulas to understand what you did, then I try to adopt it to my solution.

    At least, I have to create some KPI's which will also show the averages of the 3 groups, TOP, Middle and FLOP. But step by step!

    Again: Thanks a lot for your fast and great work here!

    😀👍👍👍😀

    BR
    Marc

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post