Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Accumulated value Groupped

Hi Guys,

(Sorry for my English)

I'm new in QlikView Development. Please have a look at Accumulated Pats, Accumulated Visits and Call Efficiency.

MonthYearTargetValue# Pats# Visits#Docs In SegmentAccumulated PatsAccumulated  VisitsCall Efficiency
Dec-11Non Target17501251=1750=1=1750/1/251
Dec-1118383839=838=38=838/38/39
Feb-12Non Target19136251=1750 + 1913=1 + 6=(1750 + 1913)/(1 + 6)//251
Feb-1217436039=838 + 743=38 + 60=(838 + 743)/(38 + 60)/39
Oct-12Non Target24041251=1750 + 1913 + 2404=1+6+1=(1750 + 1913 + 2404) / (91+6+1) / 251
Oct-1216545639=838 + 743 + 654=38 + 60 + 56=(838 + 743 + 654)/(38 + 60 + 56)/39

  I have tried to use "above" function, but it cannot be grouped with TargetValue. Maybe what I want to achieve is like the code below.

aggr(NODISTINCT Above(TOTAL Count(KeyPatient),0,RowNo(TOTAL)),TargetValue)

But my code show the same results as

aggr(NODISTINCT Count(KeyPatient), TargetValue)

TargetValue, and MonthYear is dynamic, so I cannot hard code it.

Any help would be appreciated

Thank you in advance.

Regards,

Yuli Aria Winata

5 Replies
Not applicable

Re: Accumulated value Groupped

What exactly you want achieve in this ? What output you want from above table?

anantmaxx
Valued Contributor

Re: Accumulated value Groupped

HI ,

IF in your chart  your dimension is Target value then aggr(count(keypatient),Targetvalue) will show correct result.

but if in your chart you use two dimension that is monthyear and Targetvalue then use this expression

  = aggr(count(keypatent),monthyear,targetvalue)

fot logic if you want to group key patients monthyear wise and for respective targetvalue for that monthyear

Regards,

anant

Not applicable

Re: Accumulated value Groupped

I want to get call efficiency's column. But the hardest part is to to accumulate the number of pats and visits. Um, for example :

pat A jan : 100 | acc : 100

pat B jan : 10 | acc : 10

pat A feb : 200 | acc : 300 (100 + 200)

pat B feb : 20 | acc : 30

pat A mar : 150 | acc : 450 (100 + 300 + 450)

pat B mar : 15 | acc : 45

Actually I found a way to get the call efficiency number, but it's a little bit tricky also if i changed the dimesion, the wrong value will be shown.

Call Efficiency :

if(MonthYear = TOP(TOTAL MonthYear),

     Count (KeyPatient),

     Count (KeyPatient) + TOP(TOTAL `Patients Accumulated`,if(RowNo(TOTAL)-11 < 0,

          0,

          RowNo(TOTAL)-11

     ), RowNo(TOTAL)-1)) / if(MonthYear = TOP(TOTAL MonthYear),

          Sum (Visits),

          Sum (Visits) + TOP(TOTAL `Visits Accumulated`,if(RowNo(TOTAL)-11 < 0, 0, RowNo(TOTAL)-11

     ),RowNo(TOTAL)-1)) / count(total<TargetValue> distinct KeyDoctorCnt

)

Message was edited by: yuliaria.winata

Not applicable

Re: Accumulated value Groupped

Thank you for your reply.

I have tried that way, the value will be repeated in every dimension.

MonthYearTargetValue# Pats# Visitsaggr(count(keypatent),monthyear,targetvalue)
Dec-11Non Target175011750
Dec-11183838838
Feb-12Non Target191361913
Feb-12174360743
Oct-12Non Target240412404
Oct-12165456654
anantmaxx
Valued Contributor

Re: Accumulated value Groupped

do you want # pats and # visits also in dimension

then add anew expression aggr(count(keypatent),monthyear,targetvalue,#pats,# visits)

and check whether value still repeats.

Regards,

Anant