topic Re: Average calculation at the script level in QlikView

Average calculation at the script level

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Dear All!

Appreciate, if I'm helped with, to calculate average at the script level so that I need to be able to write a nested if statement to prepare a class like (>30000, 20000-30000,10000-20000 ) etc ,based on the average computed at the script level. Since the value given is not the average the class function to be based on the average. Also, the average here should be the half of the value of each employee.

Thanks in advance

Neville

Re: Average calculation at the script level

Miguel_Angel_Baeyens — Tue, 14 Jul 2020 11:04:59 GMT

Load the data normally in the script, then, using the key field or fields, LEFT JOIN another table with the aggregation for the average, something like

LEFT JOIN (Table) LOAD
EmployeeID,
Avg(Value) AS AvgValue, // if this value needs to be divided by two, do it here
Class(Avg(Value), 'Class1', 'Class2', Class3') AS ClassValue
RESIDENT Table;

Re: Average calculation at the script level

nevilledhamsiri — Tue, 14 Jul 2020 19:07:37 GMT

Dear Migual,

Could you please see any synthetic error in my if statement at the script? I do not get it correctly at the back end.

Thanks

Re: Average calculation at the script level

Kushal_Chawda — Tue, 14 Jul 2020 21:34:25 GMT

change like below

MAIN:
LOAD BR_CODE, DATE, ME_NAME, VALUE

FROM [D:\ME_COMM.xlsx] (ooxml, embedded labels, table is Sheet1);

LEFT JOIN (MAIN)
LOAD
ME_NAME,BR_CODE,

avg(VALUE) AS AVGVALUE,
IF(avg(VALUE)>25000,'>25000',
IF(avg(VALUE)>20000,'20000-25000',
IF(avg(VALUE)>15000,'15000-20000',
IF(avg(VALUE)>10000,'10000-15000','<10000'))))AS CLASS_FLAG

RESIDENT MAIN
Group by ME_NAME,BR_CODE;

Re: Average calculation at the script level

nevilledhamsiri — Wed, 15 Jul 2020 17:22:54 GMT

Hi Kush,

It worked. I need to be more thorough in script writing familiarity. Suppose I have two files where one file carry commissions details of employees & other carry their date joined. I need to calculate the average commissions based on the employee's age. If the age is >= 12 Months as at today, the amount to be the average of twelve Months if not the amount to be divided by number of Months which may be less than 12 Months. How the script to be structured like you did it for a situation only when a single table is loaded which is the base table.I did it to some far but getting a script error. Could you please help me on this?

Thanks

Neville

Re: Average calculation at the script level

nevilledhamsiri — Wed, 15 Jul 2020 17:34:19 GMT

Dear Miguel

Thanks for your reply which helped me to gain some idea to further my interest on this.

Neville

Re: Average calculation at the script level

Kushal_Chawda — Wed, 15 Jul 2020 17:39:49 GMT

Do you need just for 12 months Avg or dynamic like if some employee completed 6 months so avg of 6 months?

Re: Average calculation at the script level

nevilledhamsiri — Wed, 15 Jul 2020 17:44:35 GMT

Yes,

but it should be at the script itself

Re: Average calculation at the script level

nevilledhamsiri — Wed, 15 Jul 2020 17:46:08 GMT

Should be varied depending on the age (Months) like you said to be dynamic

Re: Average calculation at the script level

Kushal_Chawda — Wed, 15 Jul 2020 20:50:52 GMT

you can o something like below

MAIN:
LOAD BR_CODE, ME_CODE, NAME_1, POLI_NO, V_NO, RECEIPT_NO, NAME_2, AMOUNT, VOUCHER_NO
FROM [D:\COMM_DETAILS.xlsx] (ooxml, embedded labels, table is WORKING);

LEFT JOIN (MAIN)
LOAD [Branch Code], BRANCH, [ME Code] AS ME_CODE,Sales_Person_Name, [Date Joined], Designation, Premium,
((year(today())*12)+month(today())) - (((year([Date Joined])*12)+month([Date Joined]))) as Age_in_month,
Ceded_Premium, Ceded_Comm, [Net Revenue], Commission, [Net Claims], [Gross Profit],
[Staff Cost], [Other Cost], [Net Contribution], [Debtors & Rtd Cheques over 90 Days], Salary,
EPF, ETF, Gratuity, Bonus, Medical, Insurance, [Special Allowance], Telephone, Fuel,
[Vehicle Maintenence], Insurance1, Depreciation, [Lease Cost], F32, [Resigned Date], F34, F35,
F36, F37, F38
FROM [D:\DATE_JOINED.xlsx] (ooxml, embedded labels, table is D_J);

LEFT JOIN (MAIN)

LOAD
BR_CODE,ME_CODE,

SUM(AMOUNT)/Age_in_month AS AVG_COMM

RESIDENT MAIN

GROUP BY ME_CODE,BR_CODE;

Re: Average calculation at the script level

nevilledhamsiri — Thu, 16 Jul 2020 01:39:38 GMT

Dear Kush

Your script structure was applied & it is almost there but following script error coming while data is loaded. Please see what may causes this error?

Thanks for the reply

Invalid expression
LEFT JOIN (MAIN)

LOAD
BR_CODE,ME_CODE,

SUM(AMOUNT)/Age_in_month AS AVG_COMM

RESIDENT MAIN

GROUP BY ME_CODE,BR_CODE

Re: Average calculation at the script level

nevilledhamsiri — Thu, 16 Jul 2020 16:34:12 GMT

Hi Kush,

Following error comes when the data are loaded. Could you please refer this & see what the error is? I tried to locate but no idea where it is connected with. 90% must be ok as the age calculation is happening as expected.

Neville

Invalid expression
LEFT JOIN (MAIN)

LOAD
BR_CODE,ME_CODE,

SUM(AMOUNT)/Age_in_month AS AVG_COMM

RESIDENT MAIN

GROUP BY ME_CODE,BR_CODE

Re: Average calculation at the script level

Kushal_Chawda — Thu, 16 Jul 2020 17:43:28 GMT

change below

LEFT JOIN (MAIN)

LOAD
BR_CODE,ME_CODE,

SUM(AMOUNT)/max(Age_in_month) AS AVG_COMM

RESIDENT MAIN

GROUP BY ME_CODE,BR_CODE

Re: Average calculation at the script level

nevilledhamsiri — Thu, 16 Jul 2020 18:08:05 GMT

Yet to encounter the same script error what I have noted earlier though the adjustment was done as you proposed.

Re: Average calculation at the script level

Kushal_Chawda — Thu, 16 Jul 2020 18:18:19 GMT

can you share script?