Calculate on record level and add up results

[guibsantos]

Hello Community,

I have been trying to find someone with this issue on QlikSense, but with no success..

Basically what I need is to have a measure that would multiply data in the field "Quantity" on my Database by one specific "Cost_Value", record by record, from a Master Data connected to my Database by a specific field in common "Scenario_Prod".

This calculation is only needed for certain records based on the info in the [Account] field from the Database, which has to be "12345"

I have already tried to:

1) if([Account]='12345',Cost_Value*Quantity,0) - which does not work, because I need to see this in a Pivot with all the accounts selected
and also

2) (Sum(Cost_Value)*Sum({<[Account]={'12345'}>}Quantity)) which returns a non-sense high result (Probably is summing all the values and multiplying by all the quantities for which the Account corresponds to '12345') - here when I filter by product it works, but when I deselect the products I guess it is summing all the cost and then multiplying by all the qty..

Does anyone have a similar problem and was able to solve it?

Thanks!

[rubenmarin · Accepted solution]

Hi, I think this can be done in script, just join both tables or use a mapping table to have Cost_Value and quantity in the same table (I don't know the rules to join.. maybe by scenary? or by ID?) and do the calulations on script so yo have a field with the values applied, then on front-end you only need a sum of that field.