Qlik Community

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 
Search instead 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?


Thanks in advance,


Julian

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
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.

View solution in original post

11 Replies
Highlighted
MVP & Luminary
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
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
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.

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

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
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:

LOAD

*

FROM A;

INNER JOIN

LOAD

*

FROM B;

DATA:

LOAD

*,

Calculation Expression in  earlier attached Qlikview file

RESIDENT Temp;

DROP TABLE Temp;

Regards,

Jagan.