topic Re: Aggr, count, distinct in set expression for service level calculation in App Development

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

patricesalem — Thu, 02 Sep 2021 21:37:28 GMT

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.

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.

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 :

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

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

Gui_Approbato — Thu, 02 Sep 2021 23:27:06 GMT

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.

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

patricesalem — Sat, 04 Sep 2021 21:37:28 GMT

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