Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr Function Grain Mismatch

Hello,

I'm trying to fix what I believe is a grain mismatch in an aggr function.

i have a chart with 2 dimensions, and as labels I'm trying to include

     the value (count of transactions)

      same value as a % of the primary dimension's count of transactions

I'm using a dual() function, but struggling to get the % to display.

More specifically, I'm having a hard time getting the denominator (count of transactions across my primary dimension)

I'm using the formula

sum(aggr(DISTINCT Count({<[qBV.Restriction Type]-={''}>}[qBV.Case Number]), [qBV.Primary Payer]))

where [qBV.Primary Payer] is the primary dimension. The secondary Dim is [qBV.Restriction Type].

But due to the grain mismatch, it's only displaying the percentage on one column.

Does anyone know how to adjust my work so that the denominator is calculated for each column?

I'm probably overlooking something simple, but I've tried several different versions of the expression with no success.

Your help is much appreciated!

Capture.PNG

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Possibly like this:

Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Restriction Type]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))


or


Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Primary Payer]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))

(I moved the DISTINCT to the count. If you dont need the distinct count, then remove it.)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

If you do not have all the chart dimensions in the Aggr() function, it does not produce output for the all the chart columns, so you will only get results in one column.

You need to add both chart dimensions to the Aggr(). This could change the output, so you may need to add a partial total back to correct this.

BYW I see a DISTINCT for your Sum. Should the DISTINCT not be for the Count?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Thanks for the response!

Good eye... The DISTINCT was just a left over from my testing; i forgot to get it out of there before posting the expression.

Do you happen to have any suggestions on how to add the partial total back?

Sorry, I'm just not understanding how to get it to work (without hard coding the values of my second dimension into the expression's set analysis).


The two legend values shown aren't the only possible values for the dimension. I could probably get away with making them that way, but I'd really like the chart to be more flexible.

When I add both dimensions to my chart, it just returns the values of the individual bars, making my %s = 100%

Thanks again!

jonathandienst
Partner - Champion III
Partner - Champion III

Possibly like this:

Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Restriction Type]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))


or


Sum(Aggr(Count({<[qBV.Restriction Type]-={''}>} TOTAL<[qBV.Primary Payer]> [qBV.Case Number]), [qBV.Primary Payer], [qBV.Restriction Type]))

(I moved the DISTINCT to the count. If you dont need the distinct count, then remove it.)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

The second one worked as I had hoped... thank you so much for your help!

Now I understand what you meant by adding the partial total back.

Thanks again!