Accumulated value Groupped

[#1]

Hi Guys,

(Sorry for my English)

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

MonthYear	TargetValue	# Pats	# Visits	#Docs In Segment	Accumulated Pats	Accumulated  Visits	Call Efficiency
Dec-11	Non Target	1750	1	251	=1750	=1	=1750/1/251
Dec-11	1	838	38	39	=838	=38	=838/38/39
Feb-12	Non Target	1913	6	251	=1750 + 1913	=1 + 6	=(1750 + 1913)/(1 + 6)//251
Feb-12	1	743	60	39	=838 + 743	=38 + 60	=(838 + 743)/(38 + 60)/39
Oct-12	Non Target	2404	1	251	=1750 + 1913 + 2404	=1+6+1	=(1750 + 1913 + 2404) / (91+6+1) / 251
Oct-12	1	654	56	39	=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

[#2]

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

[Anonymous]

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

[#4]

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

[#5]

Thank you for your reply.

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

MonthYear	TargetValue	# Pats	# Visits	aggr(count(keypatent),monthyear,targetvalue)
Dec-11	Non Target	1750	1	1750
Dec-11	1	838	38	838
Feb-12	Non Target	1913	6	1913
Feb-12	1	743	60	743
Oct-12	Non Target	2404	1	2404
Oct-12	1	654	56	654

[Anonymous]

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