Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following Measure called 'MV Gross CAD Percentage' calculated as followed:
SUM(PositionMVGrossCAD) / (AGGR(SUM(PositionMVGrossCAD), AccountDisplayCode))
Basically I want the % of market value for each record based on the total of the account it belongs to.
However, for some reason, qlik seems to only calculate the % for 1 record for each account even thought the total of each account is properly calculated and sum to 100%. See attachments.
Thx in advance for the help!
Try this:
SUM(PositionMVGrossCAD) / (AGGR(NoDistinct SUM(PositionMVGrossCAD), AccountDisplayCode))
It doesn't seem as though your code and description match...
What you're describing sounds like it should be:
SUM(PositionMVGrossCAD) / SUM(total <AccountDisplayCode> PositionMVGrossCAD)
Or you could try using NoDistinct to replicate the same calculation for each aggregated subject
I never used NoDistinct. How would it be done in this case?
Try this:
SUM(PositionMVGrossCAD) / (AGGR(NoDistinct SUM(PositionMVGrossCAD), AccountDisplayCode))
Well, I do have another Measure that does exactly what you describe. However, when you have all accounts selected the Measure calculates the % on the entire selection. See % Market Value CAD column of the screenshots.
Works like a charm!
Thx a lot!
This is probably happening because you haven't set in the expression to ignore certain selection you want. You can ignore every selection by adding a '1' in the beggining of the expression or you can set it to ignore specific values:
Sum({1} MyValue) // this will ignore everything and sum every MyValue available
Sum({<ParameterA= >} MyValue) // this will ignore only Parameter A and consider every other filter/value selected
Sum({1<ParameterB = {'xyz'} >} MyValue) // this will ignore everything, but Parameter B (or every other parameter set inside the set analysis)
Sum({1<ParameterC = $:: ParameterC>} MyValue) // this will respect the filter selections for ParameterC, but ignore everything else