Help needed with brackets to show trend arrow

rcorcoran — Tue, 16 Sep 2014 10:21:50 GMT

Can anyone help me with the following?? I am trying to show a trend arrow for each scenario but think my brackets are wrong.

I have got it to work for other metrics that didnt have the division in them.

Any help greatly appreciated

Rhona

Pick

(Match(ValueList('% of all attendees at ED who are discharged or admitted within 6 hours AMAU registration','% of medical patients who are discharged or admitted from AMAU within 6 hours AMAU registration','% of elective surgical inpatients who had principal procedure conducted on day of admission'),

'% of all attendees at ED who are discharged or admitted within 6 hours AMAU registration', '% of medical patients who are discharged or admitted from AMAU within 6 hours AMAU registration', '% of elective surgical inpatients who had principal procedure conducted on day of admission'),

if((Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM))>}ActualValue))>((Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_n_g.png',
if((Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))<(Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_s_r.png',
if((Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))=((Sum({$<[Improving Inpatient Experience]={2},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={1},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))), 'qmem://<bundled>/BuiltIn/arrow_e.png',

if((Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))>(Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_n_g.png',
if((Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))<(Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_s_r.png',
if((Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))=((Sum({$<[Improving Inpatient Experience]={22},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={23},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))), 'qmem://<bundled>/BuiltIn/arrow_e.png',

if((Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))>(Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_n_g.png',
if((Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))<(Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))),'qmem://<bundled>/BuiltIn/arrow_s_r.png',
if((Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM))}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM))>}ActualValue)))=((Sum({$<[Improving Inpatient Experience]={56},ReferredMM={$(#=Max(ReferredMM)-1)}>}ActualValue))/((Sum({$<[Improving Inpatient Experience]={57},ReferredMM={$(#=Max(ReferredMM)-1)>}ActualValue))), 'qmem://<bundled>/BuiltIn/arrow_e.png')))

Re: Help needed with brackets to show trend arrow

marcus_sommer — Tue, 16 Sep 2014 11:15:24 GMT

I would simplify the expression. If I understand your expression right then there are 3 blocks of arrows which return the same result - therefore you could merge them:

... Sum({$<[Improving Inpatient Experience]={2, 22, 56},ReferredMM= ...

Further you could use for such expressions parametrized variables. It meant to write the expression as variable and all dynamic values will be replaced by a $:

... Sum({$<[Improving Inpatient Experience]={$1, $2, $3},ReferredMM= ...

and the expression looks like:

$(eMyExpression(2, 22, 56))

Have a look within the help and here http://community.qlik.com/search.jspa?q=variables+parameter

- Marcus

Re: Help needed with brackets to show trend arrow

rcorcoran — Tue, 16 Sep 2014 11:30:45 GMT

Thanks marcus that makes sense will try.

Rhona Corcoran

topic Re: Help needed with brackets to show trend arrow in QlikView

Help needed with brackets to show trend arrow

Re: Help needed with brackets to show trend arrow

Re: Help needed with brackets to show trend arrow