7 Replies Latest reply: Dec 11, 2017 4:41 AM by Sunny Talwar RSS

    calculating the last but one categorie in a pivot table

    Nico Kirsch

      Hi guys,

       

      we employ various carriers. We pay about 300€ per day and now it’s my part to distribute the cost per day to the various points of delivery.

       

      Attached you’ll find a pivot table with one tour of the carrier GLS. As you can see the structure is:

       

      Spedition (carrier)
            Kennzeichen (truck)
                   tour id
                         AbladestellenNummer (number of delivery)

       

      For distributing the dayly costs I need to get the total number of deliverypoints per tour.
      By using =count(AbladestellenNummer) I receive ‘1’ for every row.
      By using =max(AbladestellenNummer) I receive the number of every single row.

      I understand why but I can’t think of a solution.
      In my attached example I would like to receive a ‘6’ in every row.

      =Max({1<tour_id={tour_id}>}abladestellenNummer) doesn’t work at all.
      How can I calculate the total in every row ignoring the "AbladestellenNummer"?

      Even in my script I didn’t get to a solution that leads to my results.

      I am handling Qlik since the 1st of October so sorry if the answer is too obvious.


      Thanks in advance
      Nico