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: 
francois_974
Contributor III
Contributor III

Group by and aggr to calculate max and min delay set analysis

Hello

i have a question that i can't resolve by myself  after time... thanks for helping

I have a table of command & delivrey like this : extract for one "code-fournisseur"

francois_974_0-1614131867343.png

I would like a table group by code fournisseur like This

Code-founisseurMax delaiMin delaiAverageMedian
510 - PROMONET PROMED337210724

 

the calculate field "max delai" is based on 'No cde (ec)' and "Max(dateRecep)-DateCommande"

When i try  : Max(Aggr(Max(dateRecep)-DateCommande, Code-founisseur))

it don't works, and i try this to:

Max(Aggr(Max(DateRecep)-DateCommande,[Nb lig Cde (lc)],[Code - fournisseurs(fr)]))

it dont works anymore?

 Can you please help me for this ?

Thanks a lot 

1 Solution

Accepted Solutions
MayilVahanan

Hi @francois_974

Use below exp:

Avg(Aggr(If(DateRecep-DateCommande> 0, DateRecep-DateCommande) ,[No Cde (ec)],[Libellé produit (lc)],[Code - fournisseurs(fr)],DateCommande,DateRecep))

Change Avg into Max, Min, Median, etc.

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

8 Replies
MayilVahanan

Hi @francois_974 

Try like below

Max(Aggr(Max(DateRecep)-DateCommande,[Nb lig Cde (lc)],[Code - fournisseurs(fr)],DateCommande))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
francois_974
Contributor III
Contributor III
Author

Hi @MayilVahanan  

Thanks for you'r quik response (i think we have a close time zone 🙂 .. iam leaving in indian ocean 🙂 

.. it works.  

I do the same for Min() and i'ts ok too.

Perhaps you can help me more  : For this expression

 Min(Aggr(Max(DateRecep)-DateCommande,[Nb lig Cde (lc)],[Code - fournisseurs(fr)],DateCommande))

I woul'd like only the value > 0 , is it possible? how can i acheive this ?

For the average, when i done that 

avg(Aggr(Max(DateRecep)-DateCommande,[Code - fournisseurs(fr)],DateCommande)) it gave me 69 day intead of 107 

i think i dont understand the formula in this set analys . 107 is the average of   day of delay not include when delay <= 0.

Nb day of delay is calculated like that in my first tab :Max(DateRecep)-DateCommande 

can you help me again ?  

 

 

MayilVahanan

HI @francois_974 

I think so, we're in same timezone.

Might be , try  like this

Min(Aggr(If(Max(DateRecep)-DateCommande> 0, Max(DateRecep)-DateCommande) ,[Nb lig Cde (lc)],[Code - fournisseurs(fr)],DateCommande)

If not, please send the sample qvw file .

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
francois_974
Contributor III
Contributor III
Author

Hello @MayilVahanan 

 

Thank you for your answer. 

It works not completly :

example:  for code-fournisseur '510-promonet' the value min&max are good but

for code fournisseur '3280-anios, instead of minvalue = 21 the result gave me 62 ?  

whether I can abuse, i dont find a way tio calculate the average and médian value. i think i don't undersant how to use the aggr function, it's very complicate for me to understand the use.. 

Thanks again for your help

- I add my qliksense qwf but i dont have a licence, i have juste a licence for qilk sense business.

 

Francois

 

MayilVahanan

HI @francois_974 

Try like this

Min(Aggr(If(DateRecep-DateCommande> 0, (DateRecep)-DateCommande) ,[Nb lig Cde (lc)],[Code - fournisseurs(fr)],DateCommande,DateRecep))

MayilVahanan_0-1614165594519.png

 

MayilVahanan_1-1614165613953.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
francois_974
Contributor III
Contributor III
Author

Good evening @MayilVahanan 

Thanks for your solution it works like a charm ! 

If you have time  for my 2 others questions, thanks for have a regard , if not , i will search again..

have a good end day

 

François 

 

MayilVahanan

Hi @francois_974

Use below exp:

Avg(Aggr(If(DateRecep-DateCommande> 0, DateRecep-DateCommande) ,[No Cde (ec)],[Libellé produit (lc)],[Code - fournisseurs(fr)],DateCommande,DateRecep))

Change Avg into Max, Min, Median, etc.

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
francois_974
Contributor III
Contributor III
Author

@MayilVahanan 

Sorry for my late feedback.. Thanks a lot it works well.

Have a good day.

F