5 Replies Latest reply: Mar 19, 2013 3:31 AM by anant dubey

# 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.

 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

Regards,

Yuli Aria Winata

• ###### Re: Accumulated value Groupped

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

• ###### 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

• ###### 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

• ###### Re: Accumulated value Groupped

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
• ###### 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