3 Replies Latest reply: Mar 11, 2012 1:30 PM by Robert Hutchings RSS

    Expression / (count(total<site,VDate> Part)) / drill down issues etc

    Robert Hutchings

      I’ve set up a simple example to explain an issue I have at work.

       

      The business is a service company. The company incurs a callout cost for each distinct site and date visit. So if two calls (phone request to repair equipment) are attended in one visit only one callout expense are incurred.

       

      I need to enter the cost (not revenue this is already in the system) in QlikView.

       

      I can easily do this by downloading into Excel and then uploading. But I have tried for days (off andf on)  to do the calculation completely in QlikView (without the download and upload) without success so far. (I'm possible missing the obvious)

       

      The fields in the example are

      • Call (unique call number when call is received)
      • VDate (Site visit date to do the repair)
      • Site (equipment location)
      • Parts (parts used)

      Assume a cost of £100 (for simplicity) per site visit

       

      The method I used was as follows. Calculate costs in QV per call, vdate, site and part and then download into excel and upload as CallExpense (refer table below)

       

      This works well. But I can not do this all in QV (as an expression preferably not script and expression) especially when filtering by various fields

       

      Thanks for any help

       

       

        • Re: Expression / (count(total<site,VDate> Part)) / drill down issues etc
          Robert Hutchings
          CallVDatesitePartcount  (1)£ see (2 )belowsum(CallExpense)
          10001/01/2012AAAC102812.5012.50
          10001/01/2012AAAL102812.5012.50
          10001/01/2012AAAp102812.5012.50
          10101/01/2012AAAC103812.5012.50
          10101/01/2012AAAp103812.5012.50
          10201/01/2012AAAC104812.5012.50
          10201/01/2012AAAL104812.5012.50
          10201/01/2012AAAp104812.5012.50
          10301/02/2012BBBC105333.3333.33
          10301/02/2012BBBL105333.3333.33
          10301/02/2012BBBp105333.3333.33
          10401/03/2012BBBC106333.3333.33
          10401/03/2012BBBL106333.3333.33
          10401/03/2012BBBp106333.3333.33
          10501/02/2012CCCC102425.0025.00
          10501/02/2012CCCC107425.0025.00
          10501/02/2012CCCL107425.0025.00
          10501/02/2012CCCp107425.0025.00
          Total


          185.56400.00

           

           

             1   Count Calculation
          count  ( total<VDate, site> Part)
          2  Call expense calculation before download  into Excel
          £100  /   (count(total<VDate,site> Part))
             

           

          Sum CallExpense is the sum from an Excel download and upload of the CallExpense field (from calculation 2 above).

            • Expression / (count(total<site,VDate> Part)) / drill down issues etc
              Stefan Wühl

              Either use a total mode 'sum of rows' for your expression (option on expression tab for a straight table),

              or use the equivalent expression using advanced aggregation for total mode expression total in a pivot table or text box:

               

              =sum( aggr(

              100  /   (count(total<VDate,site> Part))

              , Call, VDate, site, Part))

               

               

              Hope this helps,

              Stefan

                • Re: Expression / (count(total<site,VDate> Part)) / drill down issues etc
                  Robert Hutchings

                  Stefan

                   

                  Brilliant. And thanks for your prompt reply.

                   

                  I still do not fully understand aggr but will work on this next weekend.

                   

                  I had to adjust the above formula by adding {1} though

                   

                  =sum( aggr( 100  /   (count({1} total<VDate,site> Part))

                  , Call, VDate, site, Part))

                   

                   

                  This was to allow drill down as shown in the extract below (drill down by C102)

                   

                   

                  I will try this on the full database tomorrow.

                   

                  Thanks again RJ

                   

                  CallVDatesitePartsum(CallExpense)sweuhlsweuhl Adj {1}




                  37.50200.0037.50
                  10001/01/2012AAAC10212.50100.0012.50
                  10501/02/2012CCCC10225.00100.0025.00