Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
May be this
=Count(TOTAL <Spedition, Kennzeichen, [tour id]>AbladestellenNummer)
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
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?
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
Hi Sunny,
I'm just leaving to weekend.
So please don't expect my answer before monday.
happy weeeekend
Nico
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
It might be easy if you are able to share a sample...
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