Using expression in Inline table

[jimgibson]

I am loading the following INLINE table

LOAD * INLINE [
Metric, ShortDSC, LongDSC, SortOrder, MedianCalc
A-AD, Door to Admit, median of Door to Admit, 1, Median('A-AD')
A-OF, Door to On-Floor, median of On-floor, 3, Median('A-OF')
A-RD, Door to Ready for Discharge, median door to discharge, 2, Median('A-RD')
];

This loads without errors but when I try to load the Expression into the Definition box of a straight table as $(MedianCalc) I do not get any results.  Normally to use the expression I have to use Median([A-AD]) however the INLINE does not like the [] so I replaced them with '' and it loads.

Any ideas on what I am doing wrong?

[ramoncova06]

would need to look you dataset, but this seems to work ok for me

[jimgibson]

A-AD is not the field A minus the  Field AD.  There is a column in the data table I load  from SQL called "A-AD".  It is that field that I am trying to find the median of.

[ramoncova06]

this should work then, though as Peter already mentioned you need to ensure one of the metrics is always selected

[jimgibson]

Here is a sample qvw.  Hopefully this will make what I am doing more understandable

[jimgibson]

Ramon,

I have sent a sample .qvw in a separate post.

[settu_periasamy · Accepted solution]

Hi Jim,

Try this expression..

$(=concat('if(Metric=' & chr(39) & Metric & chr(39) & ',Median({1}[' & Metric & '])', ',') & concat(right(Metric&')',1)))

[Peter_Cammaert]

Very nice, and this is about the only solution to get dynamically changing expressions in a straight table.

I was going to say that your last resort would be to create a big nested IF() that tests dimension values and executes the corresponding expression. But that would be a rigid solution. Every time a new metric is added, you would have to add an expression to the IF() construct. Settu's technique avoids that problem and operates from the entire INLINE table.

This trick was invented by John W.‌ if I'm not mistaken. Store it in a safe place for whenever you need it again.

Peter

[Edit] So I was mistaken. rbecher created this technique. Credit where it's due!

[settu_periasamy]

Yes peter.. i was trying to provide the discussion link, but not able to find it..

Edit:  I think this is the one..

Chart with count of $Field values

[jimgibson]

This works and I have validated the Medians.  Thank you.

One problem and I am not sure this is possible.  It comes up correctly the first time but as you change the items in your selected fields the numbers in the table do not change.

[Peter_Cammaert]

Remove the {1} set specification from settu's expression and try again. Base set 1 means use all data and ignore all selections.