topic Re: Average Total % in App Development

Average Total %

tracycrown — Sat, 01 Jul 2023 15:34:29 GMT

Dear all

Kindly advise how to compute the correct average total% of 20%, 16.7% is wrong.

Thank you, Tracy

Re: Average Total %

Vegar — Sun, 02 Jul 2023 05:59:14 GMT

I don't have the tool to open your sample qvw at the moment 😔, but the looks of your Excel I suggest that you have trouble getting the correct average due to one row with no value.

Try these expressions

avg(Rental)/sum(total Rental)
avg(total Rental)/sum(total Rental)
(avg(Rental)/sum(total Rental))-avg(total Rental)/sum(total Rental)

Re: Average Total %

tracycrown — Sun, 02 Jul 2023 06:43:37 GMT

Dear Mr Vegar

You have resolved all my problems. Thank you very much, you are the best.

Can you advise the following with examples :

1. Eliminate all records in script for records without value.

2. Replace all records in script with zero for records without value instead of '-'

Some suggested using Alt (Rental) or Isnull(Rental), can you also give me some examples.

Thank you very much, Tracy

Re: Average Total %

BrunPierre — Sun, 02 Jul 2023 08:22:05 GMT

@tracycrown This normally does the trick of restricting the load of the null value.

LOAD *, FROM Source Where Len(Trim(Rental)) > 0 ;

Indeed, You can replace any NULL value with 0 (zero) by using the function ALT, in your case, like this.

Alt(Rental, 0) as Rental

This will be efficient where there are multiple fields.

SET NULLINTERPRET='-';
NullAsValue Rental, Field1,Field2 ;
Set NullValue = 0;

Re: Average Total %

tracycrown — Sun, 02 Jul 2023 08:09:39 GMT

Dear Mr BrunPierre

Can you use my attached rental file to illustrate your solution ?

Thank you, Tracy

Re: Average Total %

BrunPierre — Sun, 02 Jul 2023 09:10:09 GMT

Of the two, this is a better approach

LOAD Factory,
Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;

than this;

LOAD Factory,
Alt(Rental, 0) as Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);

Re: Average Total %

tracycrown — Sun, 02 Jul 2023 12:54:11 GMT

Dear Mr BrunPierre

Qlik cannot accept than this, please see below :

LOAD
Factory,
Rental
FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;
than this; <----------------- error

LOAD
Factory,
Alt(Rental, 0) as Rental
FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$);

Regards, Tracy

Re: Average Total %

BrunPierre — Mon, 03 Jul 2023 06:14:14 GMT

They're two different load scripts for handling null values.

This restricts the load of null values,

LOAD Factory,
Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$)
Where Len(Trim(Rental)) > 0 ;

While this replaces any NULL value with 0.

LOAD Factory,
Alt(Rental, 0) as Rental

FROM [...\Data-1.xls] (biff, embedded labels, table is Sheet1$);

Re: Average Total %

tracycrown — Mon, 03 Jul 2023 10:20:33 GMT

Dear Mr BrunPierre

For Multiple fields, am I correct to put the below statements at the beginning of the script before loading the file. For example :

SET NULLINTERPRET='-';
NullAsValue Rental, Act_Value, Bud_Value ;
Set NullValue = 0;

Load Factory,

Rental,

Act_Value,

Bud_Value

FROM
[Data-1.xls]
(biff, embedded labels, table is Sheet1$)