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

[francois_974]

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"

I would like a table group by code fournisseur like This

Code-founisseur	Max delai	Min delai	Average	Median
510 - PROMONET PROMED	337	2	107	24

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 

[MayilVahanan]

Hi @francois_974 

Try like below

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

[francois_974]

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 .

[francois_974]

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))

[francois_974]

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 · Accepted solution]

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.

[francois_974]

@MayilVahanan 

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

Have a good day.

F