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.
For some reason the expression is not doing this.
I hope that helps.
From what you just described, I think this should work for you:
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?
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.
Load * Inline [
'By EOC Destination','EOCDest'
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.
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.
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.
'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",
sum(Cases)+sum(Cases2) as TotalCases,
sum(if("WM Days">"LM Days","WM Days","LM Days"))as ProductionDays,
sum("Held Inventory 2") as HeldInventory
(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