Discussion Board for collaboration related to QlikView App Development.
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,
Or else try this expression
=Sum(Aggr(Avg(Hold) * Sum(Turnover), Player, Machine, Hold, Turnover))
Hope this helps you.
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.
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.
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.
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.
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?
If possible join both the tables and implement the above in script like this
Calculation Expression in earlier attached Qlikview file
DROP TABLE Temp;