Regarding Balance Amount Calculation

[#1]

Hello Friends,

I appreciate your time.  Kindly let me know the best approach to calculate the following Balance Amount.

  Customers   Milk_In_lts   Rate(Rs.)     Date             Total         AmountPaid        Balance

   Cus1           10                    25          1/1/15               250        100                           150

   Cus1           15                    20          2/1/15               300        150                            300**

(Need to calculate and maintain this  Balance  amount for all the customers)

Please let me know

Kind regards,

Kiruthi

[MarcoWedel]

Hi,

one solution could be:

hope this helps

regards

Marco

[MarcoWedel]

attached the qvw

regards

Marco

[pratap6699]

do the same caliculations on loading time it is more effeciently work...i mean good performence in qvapp

[#5 · Accepted solution]

Thank You Macro, I appreciate your help and time !!

[#6]

Hello Pratap,

Thanks and I appreciate your time!

I did the entire calculation based on RangeSum, If possible, could you post the way to do the calculations with in the script?

[MarcoWedel]

Hi,

you're welcome.

please mark a reply correct that answered your question (if there is one)

thanks

regards

Marco

[#8]

Yup that's done!! Thanks :-))

[MarcoWedel]

Hi,

one solution in the script could be:

tabSales:
LOAD *,
    Floor(Rand()*Milk_In_lts*[Rate(Rs.)],10) as AmountPaid;
LOAD RecNo() as SalesID,
    'Cus'&Ceil(Rand()*10) as Customer,
    Ceil(Rand()*10)*5 as Milk_In_lts,
    Ceil(Rand()*4)*5+10 as [Rate(Rs.)],
    Date(Ceil(Rand()*DayNumberOfYear(Today()))+YearStart(Today())) as Date
AutoGenerate 100;

Left Join (tabSales)
LOAD SalesID,
    TotalPrice,
    RangeSum(If(Previous(Customer)=Customer,Peek(Balance)),TotalPrice-AmountPaid) as Balance;
LOAD SalesID,
    Customer,
    AmountPaid,
    Milk_In_lts*[Rate(Rs.)] as TotalPrice
Resident tabSales
Order By Customer, Date, SalesID;

Hope this helps

regards

Marco

[#10]

Hello Macro,

I sincerely appreciate your time.

It works good,Thanks!

Is there any way to show only the current balance for a customer? i.e the latest balance instead of showing all the previous list of balances for a particular customer.

Kindly let me know

Thanks,

Kiru