# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for
Did you mean:
Highlighted Not applicable

## calculating expression where dimensions have a many to many relationship

Hello,

I need a hand with an expression where where the dimensions have a many to many relationship and a twist...

The dimensions are Player, Machine, Hold (%) as well as Turnover (\$).  I need to calculate how much we keep (Hold %) of the Turnover for each player

-     Each player has Turnover on multiple machines

-     Each machine has an individual Hold %.

So what I am having trouble calculating is the total \$ Spend per Player

Player          Machine          Hold           Turnover             Turnover * Hold

A B               1                    .12               100                    12.00

A B               2                    .11               110                    12.10

A B               3                    .13               120                    15.60

TOTAL \$ Spend for A B = 39.70 - how do I get this value?

Julian

1 Solution

Accepted Solutions
Highlighted MVP & Luminary

## Re: calculating expression where dimensions have a many to many relationship

Or else try this expression

=Sum(Aggr(Avg(Hold) * Sum(Turnover), Player, Machine, Hold, Turnover))

Hope this helps you.

Regards,

Jagan.

11 Replies
Highlighted MVP & Luminary

## Re: calculating expression where dimensions have a many to many relationship

Hi Julian,

Please find attached file for solution.

Regards,

Jagan.

Highlighted Not applicable

## Re: calculating expression where dimensions have a many to many relationship

Sorry Jagan.  Using an average works fine for my test data so I should have given a more extreme sample.  a small number of the machines have a hold % of .01 with large turnover and many others with hold % of up to .2 with small turnover so doing an average is not accurate.

Attached is a better example of my problem.

Highlighted MVP & Luminary

## Re: Re: calculating expression where dimensions have a many to many relationship

Hi,

Please find attached file for solution.

Regards,

Jagan.

Highlighted Not applicable

## Re: calculating expression where dimensions have a many to many relationship

Thanks Jagan,  I was wondering if I needed to put it in the script.  I'm still getting my head around scripting so will have a play.

Highlighted Not applicable

## Re: Re: calculating expression where dimensions have a many to many relationship

Hi, Please create the new field in the script by using [Hold %]*\$ AS New_Value and use SUM(New_Value).

Please find the attached file for reference.

Highlighted Not applicable

## Re: calculating expression where dimensions have a many to many relationship

Jagan,

Will this calculation work even if the two figures Hold and Turnover are from two different sources?

Highlighted MVP & Luminary

## Re: calculating expression where dimensions have a many to many relationship

Hi Julian,

Calculating in Script is the best method when compared to dynamic calculation, also for the same calculation you need to use Aggr(), for this calculation if you do this dynamically, Aggr() has performance issues sometimes.  So calculating in script works for any situation.  Check with your original data, if there are any issues let us know.

Regards,

Jagan.

Highlighted Not applicable

## Re: calculating expression where dimensions have a many to many relationship

Hi,  I am getting there (I think).  I can replicate the general calculation method in my script on two values within the same table but it does not work between two tables even if concatenated.  Is there something I need to add?

Highlighted MVP & Luminary

## Re: calculating expression where dimensions have a many to many relationship

Hi,

If possible join both the tables and implement the above in script like this

Temp:

*

FROM A;

INNER JOIN

*

FROM B;

DATA: