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

    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

        • Re: Accumulated value Groupped
          Kiran V

          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
              anant dubey

              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