Measure not being calculated for all records

[JFDemers]

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!

[Or]

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)

[therealdees]

Or you could try using NoDistinct to replicate the same calculation for each aggregated subject

[JFDemers]

I never used NoDistinct. How would it be done in this case?

[therealdees · Accepted solution]

Try this:

SUM(PositionMVGrossCAD) / (AGGR(NoDistinct SUM(PositionMVGrossCAD), AccountDisplayCode))

[JFDemers]

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.

[JFDemers]

Works like a charm!

Thx a lot!

[therealdees]

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