calculating the last but one categorie in a pivot table

[Anonymous]

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 1^st of October so sorry if the answer is too obvious.


Thanks in advance
Nico

[sunny_talwar]

May be this

=Count(TOTAL <Spedition, Kennzeichen, [tour id]>AbladestellenNummer)

[Anonymous]

Hi Sunny,

first it looked very good, but it counts EVERY point of delivery. When one tour is selected the result is as whished. I need the counting for every single tour without specific selection.

Thank you

[sunny_talwar]

May be you need this

=Count(TOTAL <Spedition, Kennzeichen>AbladestellenNummer)

If that doesn't work, would you be able to share a sample where you have not selected a tour_id and explain what is that you wish to see?

[Anonymous]

Hi again,

I tried that after your suggestion but I get the total of all AbladestellenNummers as attached.

What I'm looking for is:

for every single row the total of AbladestellenNummers of the tour ID it belongs to

Tour ID	AbladestelleNummer	AbladestellenTOTAL
555	1	3
555	2	3
555	3	3
956	1	5
956	2	5
...	...	...

This is my goal.
Now I can devide the 300€ for tour 555 into 100€ per Abladestelle (Point of delivery)

Thank you all

Nico

[Anonymous]

Hi Sunny,

I'm just leaving to weekend.

So please don't expect my answer before monday.

happy weeeekend

Nico

[Anonymous]

Hi again,

what about a new table that lists the tour_id's and counts the AbladestellenNummers (altenatively shows the max of AbladestellenNummer for every tour_id)?

I think this has to be created in the Skript. But I can't find Information how to calculate within Skript.

Is that possible?

Can someone help me?

Greetings

Nico

[sunny_talwar]

It might be easy if you are able to share a sample...

[Anonymous · Accepted solution]

I'm sorry I couldn't communicate my Problem properly and didn't managa to post an example file.

I just found out what I was looking for. I solved my Problem as follows:

Abladestellen:

load [tour_id] ,
  count([abladestellenNummer]) as abladestellenNummer_max

RESIDENT "statistik_daten" Group By [tour_id];

Thanx for trying

Nico