8 Replies Latest reply: Feb 6, 2018 9:37 AM by Nico Kirsch 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