Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rcorcoran
Contributor

Help needed with brackets to show trend arrow

Hi

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



2 Replies

Re: Help needed with brackets to show trend arrow

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

rcorcoran
Contributor

Re: Help needed with brackets to show trend arrow

Thanks marcus that makes sense will try.

Rhona Corcoran

Community Browser