Calculate Rolling Average for OTD with Null data in some months

[NAV-pchangkhaoprom]

Hello Experts,

I'm currently working on calculating the 12-month rolling average for the On-time Delivery and came across one issue as we do not have the data for some months (due to the nature of our business).

The current issue is that Qlik Sense is picking the last 12 data points which can sometimes be out of the range that I want to calculate. Is there a formula that I can use to limit the calculation to the rolling 12 months and include the month that does not have any data?

The current setup is as below;

Dimensions: Bar and Chart (Field: Delivery Date)

Measures: Height of line

(I use the expression below)

=rangeavg(above(count({<[delivery status] = {'On Time'}>} [delivery status]) / count({<[delivery status] = {'On Time','Delayed'}>}[delivery status]),0,12))

Thank you very much.

[TcnCunha_M]

Just add a date and then you can make the range, i.e

=rangeavg(above(count({<[delivery status] = {'On Time'} , you_date_Field = {">=MonthStart<=MonthEnd"} >} [delivery status]) / count({<[delivery status] = {'On Time','Delayed'}you_date_Field = {">=MonthStart<=MonthEnd"} >}[delivery status]),0,12))

Ie

=rangeavg(above(count({<[delivery status] = {'On Time'} , you_date_Field = {">=01/01/2023<=31/12/2023"} >} [delivery status]) / count({<[delivery status] = {'On Time','Delayed'}you_date_Field = {">=01/01/2023<=31/12/2023"} >}[delivery status]),0,12))

[NAV-pchangkhaoprom]

Thank you, TcnCunha_M.

I have the problem when I tried adding in the dates. I receive the error in expression when I tried to follow your guide. I'm not sure if this might be due to the date format or what causes the issue.

[Rohan]

Hi,

Try this :

={<DeliveryDate={">=$(=date(addmonths(max(DeliveryDate),-11))) <=$(=date(max(DeliveryDate)))"}>}

rangeavg(above(count({<[delivery status] = {'On Time'}>} [delivery status]) / count({<[delivery status] = {'On Time','Delayed'}>}[delivery status]),0,12))

Regards,

Rohan.

[Thiagoros]

Problems while inserting dates could be due to date format difficulties. Check the format alignment in your expression for a resolution.

[TcnCunha_M]

show the print so we can see with the expression error.

you can start more simply split the expression and be joining step by step

Expr1:
Count({<[delivery status] = {'On Time'} , your_date_Field = {">=01/01/2023<=31/12/2023"} >} [delivery status])

Expr2:

count({<[delivery status] = {'On Time','Delayed'},your_date_Field = {">=01/01/2023<=31/12/2023"} >}[delivery status])

If both expressions work isolated

Expr3:

Count({<[delivery status] = {'On Time'} , your_date_Field = {">=01/01/2023<=31/12/2023"} >} [delivery status])

/

count({<[delivery status] = {'On Time','Delayed'},your_date_Field = {">=01/01/2023<=31/12/2023"} >}[delivery status])

Then check the results of this step by step.