topic Re: Regarding Balance Amount Calculation in QlikView

Regarding Balance Amount Calculation

Tue, 02 Jun 2015 18:50:29 GMT

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

Re: Regarding Balance Amount Calculation

MarcoWedel — Tue, 02 Jun 2015 21:42:07 GMT

Hi,

one solution could be:

hope this helps

regards

Marco

Re: Regarding Balance Amount Calculation

MarcoWedel — Tue, 02 Jun 2015 21:43:38 GMT

attached the qvw

regards

Marco

Re: Regarding Balance Amount Calculation

pratap6699 — Wed, 03 Jun 2015 05:30:24 GMT

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

Re: Regarding Balance Amount Calculation

Wed, 03 Jun 2015 19:17:02 GMT

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

Re: Regarding Balance Amount Calculation

Wed, 03 Jun 2015 19:19:11 GMT

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?

Re: Regarding Balance Amount Calculation

MarcoWedel — Wed, 03 Jun 2015 19:30:41 GMT

Hi,

you're welcome.

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

thanks

regards

Marco

Re: Regarding Balance Amount Calculation

Wed, 03 Jun 2015 20:29:14 GMT

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

Re: Regarding Balance Amount Calculation

MarcoWedel — Wed, 03 Jun 2015 21:06:25 GMT

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

Re: Regarding Balance Amount Calculation

Wed, 03 Jun 2015 21:37:41 GMT

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

Re: Regarding Balance Amount Calculation

MarcoWedel — Wed, 03 Jun 2015 22:18:59 GMT

Hi,

I'm currently working on a solution to only show the latest balance.

Maybe in the meantime you can mark any reply correct that actually answered your initial question.

thanks

regards

Marco

Re: Regarding Balance Amount Calculation

MarcoWedel — Fri, 05 Jun 2015 22:53:26 GMT

Hi,

one solution to only show current balances could be:

hope this helps

regards

Marco

Re: Regarding Balance Amount Calculation

Sat, 06 Jun 2015 02:52:45 GMT

Hello Macro,

I sincerely appreciate your time and help!!

Thanks, I got it :-)))

Kiru