topic Re: Create a new Function in QlikView

Create a new Function

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I would like to create a new function that I can use to display in a statistics box. Basically the function would do this equation:

(Total No. Hours) x (Hourly Rate) = Total Service Pay

I was trying this lines on the script but can't seem to make it work.

Sum([# of Hours]) as TotalHours

Sum([# of Hours])*[Hourly Rate] as TotalServicePay

I guess making a function is better. So I can display it on a statistics box.

Re: Create a new Function

migueldelval — Tue, 14 Jun 2016 08:44:22 GMT

Hi Charles,

Could you attach your script?

Regards

Miguel del Valle

Re: Create a new Function

Digvijay_Singh — Tue, 14 Jun 2016 08:47:58 GMT

When you use Aggregation functions in script you need Group By clause in the load statement, can you share your script code to understand and also share what problem you are facing.

Re: Create a new Function

syukyo_zhu — Tue, 14 Jun 2016 08:48:59 GMT

Hi, if you do this on your script?

([# of Hours])*[Hourly Rate] as TotalServicePay

Re: Create a new Function

juleshartley — Tue, 14 Jun 2016 08:50:24 GMT

We really need to see the whole load statement to understand where the problem lies...

Re: Create a new Function

Digvijay_Singh — Tue, 14 Jun 2016 08:51:14 GMT

Something like this -

Load A,

Sum(B) as B1,

Sum(C) as C1,

from test.qvd

group by A,D;

Re: Create a new Function

juleshartley — Tue, 14 Jun 2016 08:53:50 GMT

ps. An immediate issue is that you are not aggregating the 'Hourly rate' field... you need to multiply before the sum (adding dimensions etc. in group by clause and load)

Load

sum(Pay) as Total Pay

...

;

Load

[# of Hours]*[Hourly Rate] as Pay

...

Re: Create a new Function

Tue, 14 Jun 2016 09:19:04 GMT

Hi Everyone,

Thanks for your response. Attached is here is the script. I was trying to add these lines at the end of the script. Not sure if I'm doing it right.

DataResults:

LOAD [# of Hours]

Sum([# of Hours]) as TotalHours,

Sum([TotalHours])*[Hourly Rate] as TotalServicePay;

Re: Create a new Function

Anonymous — Tue, 14 Jun 2016 09:25:25 GMT

Hi Charles,

Try this on the Statistics Box

Sum(Aggr(Sum([# of Hours]*[Hourly Rate]),[Personnel Name]))

Regards!!

Re: Create a new Function

Tue, 14 Jun 2016 09:52:28 GMT

Hi Manuel,

Thanks! It worked. Aggr( ) did the job.

Re: Create a new Function

Anonymous — Tue, 14 Jun 2016 10:40:15 GMT

Hi Charles,

This is not right:

You are not calling a table.
Your files are not on the same table.
You ar not doing a group by to accumulate values.

But with my other response you will have the correct result.

Regards!

Re: Create a new Function

Anonymous — Tue, 14 Jun 2016 10:40:29 GMT

You're wellcome!