Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Does a Max() work within an AVG()?

Hi everyone,

To sum up, I have a table with Order Number, delivery date and delivery time.

Since an order can have more than one date of delivery I use a max function to consider only the longest delivery time:

if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0,Max(NetWorkDays ([OrderDate],DeliveryDate)-1))

This function work correctly and I would like to get the AVG() of all these delivery time within a KPI.

I've tried a few function such as the following one but w/o success:

if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0,avg(Max(NetWorkDays ([Date_Commande_SCO],DeliveryDate)-1)))

Thank you in advance for your help

Florian

1 Solution

Accepted Solutions
pathiqvd
Contributor III

Re: Does a Max() work within an AVG()?

Hi,

   Try like this,

avg(if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0, aggr(Max(NetWorkDays ([Date_Commande_SCO],DeliveryDate)-1),[Date_Commande_SCO])) )

Regards,

3 Replies
pathiqvd
Contributor III

Re: Does a Max() work within an AVG()?

Hi,

   Try like this,

avg(if((OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0),

0, aggr(Max(NetWorkDays ([Date_Commande_SCO],DeliveryDate)-1),[Date_Commande_SCO])) )

Regards,

agigliotti
Honored Contributor II

Re: Does a Max() work within an AVG()?

maybe this:

=avg( aggr(

if( OrderDate>DeliveryDate or (NetWorkDays ([OrderDate],DeliveryDate)-1)<0 ),

0,

Max( NetWorkDays([Date_Commande_SCO],DeliveryDate)-1 ), [Order Number] ) )

Not applicable

Re: Does a Max() work within an AVG()?

It's working with you solution Iakshmipathi! Thanks to both of you for your help!

Just to know I had to change the dimension of my table because some data where missing and now to my table take into account all the delivery time for one order and not only the longest one such as:

Order: 01.01.2017

Date of delivery1: 03.01.2017, delivery time 2 days

Date of delivery2: 04.01.2017, delivery time 3 days

I would like to get only 3 days as a result of my function:

if((OrderDate_SCO>DeliveryDate_SLC or (NetWorkDays ([OrderDate_SCO],DeliveryDate_SLC)-1)<0),

0,Max(NetWorkDays ([OrderDate_SCO],DeliveryDate_SLC)-1))

Do you have any idea what's wrong with this function?

Regards,

Florian