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: 
rittermd
Master
Master

Expression Issue

Hopefully I can explain what is happening clearly here.

I have a straight table with about a dozen measures.

I change the Dimension of this table based on a list box choice.  That works fine.

The results that I get are correct for 10 of the 12 Dimensions.  Two don't work the same and I can't figure out what is different.

If I use this expression I get the correct grand total but the rows for the 2 Dimensions in question do not calculate correctly and many of them just display a - in a grey box.

Avg(Aggr(Sum(DISTINCT LOS),CaseNumber)).  But only for the two dimensions.

If I change to this expression then I get results in every row (the - grey box goes away) but the results are not correct.

Avg(Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber)) for these two dimensions. 

I had actually created this expression so that it would change the expression being used based on the Dimension picked.

If (Match(Display, 'By FDScale','By MeasureDesc'),Avg(Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber)),Avg(Aggr(Sum(DISTINCT LOS),CaseNumber)))

I am not sure what else to look at.

14 Replies
sunny_talwar

Give this a shot:

Avg(Aggr(Sum(DISTINCT LOS),CaseNumber, $(='[' & only(Dim1) & ']')))

rittermd
Master
Master
Author

This seems to have fixed the calculations for each row.  However, the grand total is off compared to the other Dimensions. 

So I will dig into this further and see what the issue might be.  Or it could be correct. 

I am attempting to clone an existing report that is very flawed.  So I don't necessarily have correct numbers to compare to. 

Thank you so much for your help.

sunny_talwar

What is the expectations with the totals? Sum of rows? Avg of rows? something else?

Also is this in a straight or pivot table? You can always use Total mode for straight table.

rittermd
Master
Master
Author

It is a straight table.  I would have expected the grand total to be the same for every dimension.  They are for most of the measures.

But again at this point I am not 100% sure without digging a little deeper.

In this case it should be the total average LOS for all cases.

sunny_talwar

If you grant total is an average, then an average based on one dimension most likely won't equal average based on another dimension. Sum's are a different story (they should match up).