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: 
patricesalem
Creator II
Creator II

Aggr, count, distinct in set expression for service level calculation

Hello

I'm trying to calculate a supply chain service level and display it in a table + bar chart.

Each order is split and x lines and the order is considered as delivered on time if all lines have been delivered on time.

patricesalem_0-1630617326870.png

For the above order, it has not been delivered on time as one line was shipped late.

I  put together a summary table to diplay orders that have delivered on time or not.

patricesalem_1-1630617660147.png

Then, I have tried to use the same expression to display the service level per week in a bar chart

Unfortunatelly, the expression I use in the table does not work for the chart. So, I've tried to use different aggr, count and distinct functions

The following expression lead me to the best result but it does not work for all orders :

Count(DISTINCT {<[COMMANDE] = {"=Count({<[SM_REELLE_shipped_auto] = {""<=$(=date(max((DATE_REF_TAUX_SERVICE))))""}>}[POSTE]) = Count([POSTE])"}>} [COMMANDE])

ie, for the following order (and also for the first table at the top of the post), it is considered as beeing fully delivered on time :

patricesalem_0-1630618614912.png

 

I guess the issue comes from the date(max((DATE_REF_TAUX_SERVICE))))  - if I remove the max then no DATE_REF_TAUX_DE_SERVICE is returned resulting in a 0 result for all orders.

Any idea ?

Thanks

Pat

 

 

1 Solution

Accepted Solutions
Gui_Approbato
Creator III
Creator III

Hi,

I think you could create a dimension to have "on time" vs "not on-time" deliveries, and count the number os orders. You can also make a measure, but check if this would work:

Aggr( nodistinct

if( Count(distinct [On time or not]) = 1 , 'On time', 'Not on time' ),

          [Order number], [Order line number], [Date to be delivered], [Delivered on] )

 

And then, count the distinct number of orders.

View solution in original post

2 Replies
Gui_Approbato
Creator III
Creator III

Hi,

I think you could create a dimension to have "on time" vs "not on-time" deliveries, and count the number os orders. You can also make a measure, but check if this would work:

Aggr( nodistinct

if( Count(distinct [On time or not]) = 1 , 'On time', 'Not on time' ),

          [Order number], [Order line number], [Date to be delivered], [Delivered on] )

 

And then, count the distinct number of orders.

patricesalem
Creator II
Creator II
Author

Hello

thanks for your tips. 

Initially, I didn't want to create a "on time"  dimension but at the end of the day, no choice and the result is good !

I have adapted your formula to get it working with all examples I found in my db :

=if (
sum(Aggr( sum(distinct [POSTE_ON_TIME_ZSQ]),[COMMANDE],[POSTE], [YEAR_WEEK_REF_TAUX_SERVICE]))
=
aggr(count(distinct [POSTE]),[COMMANDE],[YEAR_WEEK_REF_TAUX_SERVICE])
,
1,
0)

big THANKS