Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

calculating the last but one categorie in a pivot table

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

8 Replies
sunny_talwar

May be this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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 IDAbladestelleNummerAbladestellenTOTAL
55513
55523
55533
95615
95625
.........

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
Not applicable
Author

Hi Sunny,

I'm just leaving to weekend.

So please don't expect my answer before monday.

happy weeeekend

Nico

Anonymous
Not applicable
Author

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
Not applicable
Author

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