14 Replies Latest reply: Jun 3, 2016 3:41 PM by Sunny Talwar

# 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.

• ###### Re: Expression Issue

Don't like the idea of doint this, but may be try this:

Avg(DISTINCT Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber))

Also this seems like the issue related to grain mis-match. Would you be able to provide a sample to help you better here

• ###### Re: Expression Issue

This does not give the correct results for any of the dimensions.

• ###### Re: Expression Issue

Would you be able to provide a sample to look at?

• ###### Re: Expression Issue

Here is some more details that I have been able to figure out so far.

This expression works for the two dimensions when there is only 1 row for each casenumber

Avg(Aggr(NODISTINCT Sum(DISTINCT LOS),CaseNumber))

However, I have found some instances where a casenumber could have multiple rows because of some other data value.  In this case the calculation is off.

So for example I have 6 cases with 1 row each.  The dashboard correctly adds up the LOS values and then divides by 6 to get the average.

Now lets say that I have 5 cases but 6 rows because one of the cases has two rows.  The above expression is adding up the values for each of the 6 rows and diving by 6. Or 139/6 = 23.17. (See values below).

It should be 86/5 = 17.2.  So you drop the duplicate 53 and divide by the 5 actual cases.

Case    LOS

1     2

2     5

3     7

4     19

5     53

5     53

For some reason the expression is not doing this.

I hope that helps.

• ###### Re: Expression Issue

From what you just described, I think this should work for you:

Avg(Aggr(Sum(DISTINCT LOS),CaseNumber))

Can you try this as well:

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

I think you mentioned that you are selecting the dimension based on this if statement, right?

• ###### Re: Expression Issue

I think what I am trying to suggest is that, in addition to CaseNumber, you also need the dimension from the chart. Because its not there you might be seeing the issue. If you can provide more details about your dimension, we might be able to help you better

• ###### Re: Expression Issue

Pitfalls of the Aggr function

• ###### Re: Expression Issue

The first expression you gave only displays a value on the dimensions where there are multiple rows for a single case.  Otherwise I just get a - in a grey box.

The 2nd expression returns no values for anything.

Basically what I have done is taken 10 tabs in an excel spreadsheet and made it into a single chart in Qlik.  Each tab displayed the same metrics but for a different dimension.

I have a list box with the value for what used to be each tab (by FD Scale, by MeasureDesc, etc.)  I do an inline load that correlates each of these tab values to the actual field name in the data that needs to be used for that choice.

Dimensions:

Display,Dim1

'By District','District'

'By Region','Region'

'By EOC Destination','EOCDest'

'By FDScale','MeasureGroup_Scale'

'By MedicalDx','MedicalDxDesc'

'By ReferralSource','ReferralSourceID'

'By PrimaryProgram','PrimaryProgramDesc'

'By SecondaryProgram','SecondaryProgramDesc'

'By MeasureDesc','MeasureDesc'

'By DiagnosticGroup','DiagnosticGroupDesc'

];

The Dimension for my Qlik table is

=\$(='[' & only(Dim1) & ']')

Everything works perfectly except for the two Dimensions that I mentioned.  I am working with my data guy to confirm that I have the correct data also.

• ###### Re: Expression Issue

Give this a shot:

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

• ###### Re: Expression Issue

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.

• ###### Re: Expression Issue

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.

• ###### Re: Expression Issue

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.

• ###### Re: Expression Issue

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).

• ###### Re: Expression Issue

Hello Mark,

I had an exact same trouble on building expression before. I could not figure out a way until  I ended up using group by in data load editor.

"Date",

'New York' as Plant,

sum("WM Loins") as "WM",

sum("WM Days")as "WM Days",

sum(Cases) as WMCases,

sum("LM Loins")as "LM",

sum("LM Days") as "LM Days",

summ(Cases2)as LMCases,

sum(Cases)+sum(Cases2) as TotalCases,

sum(if("WM Days">"LM Days","WM Days","LM Days"))as ProductionDays,

sum("Held Inventory 2") as HeldInventory

FROM [lib://\$(vFileName)]

(ooxml, embedded labels, table is KPI)

Group by "Date",'New York';

after I did that, my Aggr expression worked very well. because this group by will eliminate your mutilple rows with same field.