calculating expression where dimensions have a many to many relationship

[#1]

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?

Thanks in advance,

Julian

[jagan]

Hi Julian,

Please find attached file for solution.

Regards,

Jagan.

[#3]

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.

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[#5]

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.

[#6]

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.

[#7]

Jagan,

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

[jagan]

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.

Please close this discussion by giving correct and helpful answers to the posts which are helpful to you.

Regards,

Jagan.

[#9]

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?

[jagan]

Hi,

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

Temp:

LOAD

*

FROM A;

INNER JOIN

LOAD

*

FROM B;

DATA:

LOAD

*,

Calculation Expression in  earlier attached Qlikview file

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.