topic Re: Invoice amount in QlikView

Invoice amount

surajap123 — Tue, 22 Apr 2014 11:40:18 GMT

Hi All,

Could you please let me know what the functionality of below code.

InvoiceTemp:

LOAD

InvoiceLineId,

InvoiceType,

((Amount / Discount) / (INTERVAL(DATE(InvoiceToDate) - (InvoiceFromDate),'K')+1))) AS InvoiceAmount,

Date(InvoiceFromDate + IterNo() -1 ) AS InvoiceDate

FROM Invoice

WHILE IterNo() <= Date(InvoiceToDate) - InvoiceFromDate +1;

Invoice:

LOAD

intInvoiceLineId,

InvoiceType,

InvoiceDate,

SUM(InvoiceAmount) AS InvoiceAmount

RESIDENT InvoiceTemp

GROUP BY InvoiceDate,InvoiceType, InvoiceLineId;

Is it necessary to create the above aggregated table(ie Invoice) to get the InvoiceAmount for a particulary point of time?

Regards,

Suraj

Re: Invoice amount

its_anandrjs — Tue, 22 Apr 2014 11:49:23 GMT

Yes it is necessary and grouping is also necessary by each dimension other wise error occurs.

Re: Invoice amount

surajap123 — Tue, 22 Apr 2014 11:51:00 GMT

Hi Anand,

Could you please let me know what is going on in the code..

Re: Invoice amount

its_anandrjs — Tue, 22 Apr 2014 11:55:50 GMT

In the above code in table InvoiceTemp there is a loop between InvoiceToDate and InvoiceFromDate until the loop satisfy the condition loop running on and calculate the InvoiceAmount.

And in the Invoice table calculate the SUM of InvoiceAmount and aggregate the InvoiceAmount with the dimension

intInvoiceLineId,

InvoiceType,

InvoiceDate

Yes it is necessary but you can calculate in the front end also and aggregate the Sum InvoiceAmount in the front end also by like this

Aggr(SUM(InvoiceAmount),intInvoiceLineId,InvoiceType,InvoiceDate)

But if you done this in the Invoice table no need to do aggr in front end just you have to use SUM(InvoiceAmount)

Re: Invoice amount

surajap123 — Tue, 22 Apr 2014 12:19:40 GMT

Hi Anand,

Thanks for the explanation !!

Due to the loop, the InvoiceTemp table is creating millions of rows. The actual invoice table (the source for InvoiceTemp) contain very few rows.

This is the table in dashboard which takes long time to load data.

Is there any alternate to calculate the InvoiceAmount?

Re: Invoice amount

its_anandrjs — Tue, 22 Apr 2014 12:31:42 GMT

As i understand you can do one thing in InvoiceTemp only calculate (Amount / Discount) and take a field InvoiceAmount not divide it with the Interval value and calculate intervals in separate table and in front end divide with this value with dateinterval value. I believe it decreases the calculation time of the InvoiceAmount because if you have so many dates then the calculation is going on and takes more time to calculate the table. And another way is use Interval in the charts.

Regards

Re: Invoice amount

surajap123 — Tue, 22 Apr 2014 13:00:12 GMT

Hi Anand,

Thanks a lot for the information.

I have difficulty working on what you suggested.

I have build a sample app. could you please calculate intervals in separate table and provide a sample front end as you suggested. This will really help me.

Regards,

Suraj