count distinct for service level calculation - not working perfectly as intended

[patricesalem]

Hello

a while back @stevejoyce gave me a great hand to help me finalizing the calculation of my company service level.

It's working great except on some cases.

I have a table summarizing the service level for all orders to be shipped on a given week - looking at a particular order, the table shows :

For the same order my count distinct formulas indicate that it's been shipped late and on time.

The order containts various lines, some have been shipped on time, others not :

As you can see 3 lines have been shipped on time and complete on week 202206, 7 lines have not been shipped on time on complete.

My summary table contains the following formula to calculte if the order has been shipped on time and complete

=count (distinct {<[Unique_ID]={"=[Planned shipment week]=[Shipment week] "},
line_shipped_on_time_complete={1}>}[COMMANDE])
It considers that if at least 1 line has been shipped on time, the full order has been shipped on time.
I would like the calculation to indicate that the order is considered as shipped on time and complete if all the lines of the order are shipped on time.
I don't know how to do it.

My summary table contains the following formula to calculte if the order has been shipped late

count (distinct {<[Unique_ID]={"=[Planned shipment week]=[Shipment week] "},
line_shipped_on_time_complete={0}>}[COMMANDE])

That's working fine as as soon as one line is late, the full order is considered as being late.

Thanks a lot in advance for your help
Pat

[anthonyj]

Hi @patricesalem,

This is the perfect use case for the P( ) and E( ) functions in Qlik. Set Analysis usually searches across rows and associations to the row to aggregate but these "Possible" and "Exclude" functions have the ability to search vertically through your data and return a list of values to pass through to your set analysis:

count (distinct {<
[Unique_ID] = E({1<line_shipped_on_time_complete={0}>}[Unique_ID])

>}[COMMANDE])

The exclude function compiles a list of all the [Unique_ID] that are flagged as 0 and removes all those ID's from the calculation of your measure. This way it doesn't matter if an ID has 10 records flagged as 1, if there's even one 0, it will be removed from calculation.

I hope this helps.

Regards

Anthony

[patricesalem]

Hi @anthonyj 

thanks for your help, I didn't know these functions.

I though it did the job in the first place.

I might know why. The function removes all the same unique_id if at least one line is=0.
Nevertheless, for a same order (COMMANDE), I have a x lines of order (each line has a unique ID).
So basically, the count function should returns 0 for the "commande" (order) if at least an order line has line_shipped_on_time_complete=0

it's not the case with your proposal :

a solution would be

count (distinct {<
[COMMANDE] = E({1<line_shipped_on_time_complete={0}>}[COMMANDE])

>}[COMMANDE])

but if I do so, my detailed table is not "linked" anymore to my summary table. So I have to keep Unique_ID in my formula

any more idea?

pat

[patricesalem]

hi @anthonyj  and @stevejoyce 

Is there any tip you could give to try to find a solution. 
if necessary I can share a sample of my qvf to make testing available for those who could give me a hand. 

thanks a lot for your help !

[patricesalem]

I have tried to use :

count(distinct if (count({<[Unique_ID],POSTE_ON_TIME_ZSQ={1}>}[COMMANDE])=count({<[Unique_ID]>}[COMMANDE]),[COMMANDE]))

but I can't use nested aggregation formula, is telling me Qlik...

so back to square

[anthonyj]

Hi,

Sorry, You're right it should have been [COMMANDE] (Order No) in the exclusion not the [Unique_ID]. This will flag the whole order as arriving on time or not but it seems like that the relationship to another summary table is not correct?

A QVF with some dummy data would be great. An example of the expected outcome for the table would help as well so we have a target to work towards.

Thanks

Anthony

[patricesalem]

Hi Anthony

You're right by indicating [COMMANDE] in the exclusion, I would get the correct result but then my relationship between the summary table and the detailed table would not work. Because of granularity in my data structure, I must keep a unique ID for each line of order that is repeated week after week until the order is fully delivered. Thanks to the support of  @stevejoyce at the beginning of my project, I found out how to set up correctly my data structure...but now, as you understand, it leads me to this new calculation issue...it's a never ending story !

Anyway, I have compiled an example with 2 orders

On week 2022_06, the summary table should tell me : 2 orders shipped, one on time, the other one late. This is not what I get

The details of these orders on week 2022_06 says :
one order (2264668) composed of 2 lines - both are shipped on time
one order with 9 lines : 3 lines are shipped full and on time. the other ones have not been shipped yet (or at least not full) :

I have attached the QVF to help you run some tests...hopefully you will be better than me !

thanks again

pat

[patricesalem]

hi @anthonyj 

by any chance, have you found the time to download the qvf and try some expressions ?

thanks 

pat

[anthonyj]

Hi @patricesalem,

Sorry I haven't been able to get back to you sooner. 

Yes, keeping grain in mind is tricky. I've been looking at your QVF file and added [COMMANDE]  as a replacement to the exclusion as we mentioned. I might be missing something but it is coming up as you explained. 2 Orders shipped, 1 late and 1 on time.

count (distinct {<COMMANDE=E({1<POSTE_ON_TIME_ZSQ={0}>}COMMANDE)>}[COMMANDE])

Since your table is at the orders level I also changed the "Orders Shipped Late" column so it is the opposite of the "Shipped on time". This populates the "1" all the way through the weeks calculation.

count (distinct {<COMMANDE=P({1<POSTE_ON_TIME_ZSQ={0}>}COMMANDE)>}[COMMANDE])

I noticed that you had set up a button to show/hide a column. I picked up a trick from Patric Nordstrom in one of his reports that puts this into one button to optimise space.

I've attached the altered qvf.

Regards

Anthony