topic Re: distinct calculation without using Aggr() - Performance in QlikView

distinct calculation without using Aggr() - Performance

Sat, 22 Aug 2015 14:51:00 GMT

I have an application where I am trying to improve performance.

Initially I had two separate tables:

Timesheets (2.4 million rows):

BillingLineID	Charge	ClientID
12345678	120	123
87654321	80	456
45678912	110	789
76543219	200	234

Billing (2.7 Million rows)::

BillingLinedID	Invoice Amount	InvoiceDate
12345678	40	1/1/2015
87654321	80	1/1/2015
12345678	40	1/2/2015
12345678	40	1/3/2015

As part of my charts and calculations throughout my application I am performing a Sum(Charge) and Sum(InvoiceAmount) based on different clients, dates etc. Lots of calculations. These two charts are linked in BillinLineID. As an example if I want to find out the total billed for a customer. A client is selected in my application, this limits the results in the Timesheets Table, which then brings in the correct results from my Billing Table.

To improve performance I am able to bring the data into QlikView in one table or perform a join in my Laod script based on BillingLineID. The problem this causes is I now have a table which looks like this:

BillingLineID	Charge	InvoiceAmount	ClientID	InvoiceDate
12345678	120	40	123	1/1/2015
12345678	120	40	123	2/1/2015
12345678	120	40	123	3/1/2015
87654321	80	80	456	1/1/2015

So now obviously when I perform a sum(Charge) based on the clientID of 123 I receive a total of 360 instead of 120. I was able to fix this by using the following expression sum(aggr(Charge,BillingLineID)) However I am using this for lots of calculations throughout my application and believe it will not be good for performance.

I would really appreciate any suggestions on how to meet my needs in a way which will have the best performance.

Thank you,

Ryan

Re: distinct calculation without using Aggr() - Performance

Anonymous — Sat, 22 Aug 2015 15:50:26 GMT

hi Ryan,

use below expression:

sum(aggr( sum(InvoiceAmount),BillingLineID))

drop charge field while joining

Load

BillinLineID,

sum(InvoiceAmount) as Charge

from.....

Group by BillinLineID;

and use this charge field for calculations

Regards

Neetha

Re: distinct calculation without using Aggr() - Performance

Sat, 22 Aug 2015 15:57:04 GMT

Hi Neetha,

The formula sum(aggr(Charge,BillingLineID)) is what I am currently using and it works fine, however I don't want to have to use the Aggr function due to performance issues, it seems to take a long tim to calculate.

Also I should mention there are lots of other fields in the tables used and the charge doesn't always have a bill associated with it i.e. if a timesheet has been completed, this will create a value in charge, however there will not be a value in invoice amount until the work has actually been invoiced. Sometimes it never gets invoiced and there is another field called WrittenOffValue if the work gets written off.

I hope this extra detail helps.

Ryan

Re: distinct calculation without using Aggr() - Performance

Kushal_Chawda — Sat, 22 Aug 2015 16:00:08 GMT

Use sum(DISTINCT Charge) instead of Aggr function

Re: distinct calculation without using Aggr() - Performance

Sat, 22 Aug 2015 16:03:09 GMT

This will not work as I want to do a sum of charge but based on the BillingLineID being the same. i.e. there will be loads of instances of the charge equaling 40 across different timesheet entries.

Re: distinct calculation without using Aggr() - Performance

Anonymous — Sat, 22 Aug 2015 16:16:52 GMT

Hi Ryan,

Does charge is equal across all invoice amounts(i.e all invoice amounts are same) for each billinglineid's

Regards

Neetha

Re: distinct calculation without using Aggr() - Performance

Kushal_Chawda — Sat, 22 Aug 2015 16:23:51 GMT

Please post the sample app