Expression Issue

[rittermd]

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.

[sunny_talwar]

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

[rittermd]

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

[sunny_talwar]

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

[rittermd]

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.

[sunny_talwar]

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?

[sunny_talwar]

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

[sunny_talwar]

Read about Grain Mismatch here:

Pitfalls of the Aggr function

[rittermd]

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:

Load * Inline [

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. 

[isaaclin]

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.

LOAD

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

Hope this will help you