Invoice amount

[surajap123]

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

[its_anandrjs]

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

[surajap123]

Hi Anand,

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

[its_anandrjs]

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)

[surajap123]

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?

[its_anandrjs]

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

[surajap123]

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