4 Replies Latest reply: Mar 11, 2014 7:00 AM by Erik Furlanis RSS

    pivot with a mix of percentages and absolute values

      dear all,

       

      I need to create an hybrid absolute/percentage values pivot chart, which I think is pretty challenging and have no idea how to solve it.

       

      my source datatable is organised per lines shipped, with some information to measure shipment performance (order date, shipped date etc) and some general info (typology of customer, urgency of order, country etc.)

       

      the expected outcome looks somewhat like below

       

      UrgentNon Urgent
      business groupCustomercountryon time+1 daytotalon time+1daytotal
      AxA185%89%1254663%70%4534
      AxA284%89%785678%81%

      5427

      AxA378%80%54682%85%43453
      AyB189%92%4538575%80%543
      AyB292%95%

      5468

      72%79%

      48634

      BzC191%96%274371%75%45385
      BzC276%84%325479%83%2424

       

      basically, the total is showing the absolute number of order lines per lowest detail (e.g., 12546 lines of urgent orders shipped to country A1, for customer x, for product type A)

      the <on time> and <+1day> show respectively the percentage of lines shipped on time and cumulative percentage of lines shipped with 1 day delay (e.g. 85% of the 12546 lines were shipped on time and 89% of the 12546 lines were shipped with a delay of 1 day)

       

      how easily implementable is this in QV?

      thank you

        • Re: pivot with a mix of percentages and absolute values
          Oleg Troyansky

          Looks like a fairly easy task:

           

          You will have 4 dimensions (Busines group, Customer, Country and Urgent/Not Urgent) and 3 Expressions - On time, +1Day, and Total. Each expression will have its own formula to calculate the result and its own number formatting.

           

          Am I missing any hidden problems?

           

          cheers,

           

          Oleg Troyansky

            • Re: pivot with a mix of percentages and absolute values

              Dear Oleg,

               

              I missed to explain it correctly.

              'On time', '+1day' (and 'delayed' , not to be visualised) are the three values to be found in another dimension, Delay.

              The expression is simply count of orderlines

               

              the first 85%:

              is the count of orderlines which are urgent order, business group A, customer x, country A1 which are shipped on time, divided by the total count of orderlines which are urgent order, business group A, customer x, country A1

               

              then the 89%:

              is the count of orderlines which are urgent order, business group A, customer x, country A1 which are shipped on time cumulated to the lines which are shipped +1day, divided by the total count of orderlines which are urgent order, business group A, customer x, country A1

               

              the 12546 is the total count of orderlines which are urgent order, business group A, customer x, country A1

               

              I hope it is clearer!

              thank you