Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am having a problem to calculate avg price, can anybody suggest some approach.
Scenario:
I have a table contains 'billing date' , 'billing value' , 'Cust_ID' and 'billing qty'.
I have to calculate each customer's Avg price.
there is a logic to calculate avg price which Is like if I select a date from the table then from this selected date to last two month
for eg if I select date 13-10-2013. then from this date to previous last two month (September & august) i.e 13-10-2013 to 01-08-2013.
so for this particular date the avg price would be (total Billing Value from 13-10-2013 to 01-08-2013) / (Total Billing Qty from 13-10-2013 to 01-08-2013).
Data is attached for reference.
Thanks in advance to community
See attached qvw.
Thanks for replying Wassenaar.
Pls also check my output table in excel sheet where I have calculated rec no 30 as a example.
so for each record the calculation logic for avg price would be same.
The output table in your excel file does not make sense. There data is different from that of the input table. The totals of the output table also do not make sense. Why is one of the records for 31-08-2013 not included in the calculation but the others are? There's no information that tells me why. And 25222365 divided by 54 is 467080.83 not 46710.
I Agree with your answer.
in my case the input sheet is a source to QV.
in scripting I have to apply a logic that calculates avg price for each customer starting from day(Billing_Date) to last two month.
I just tried to explain rec 30 as an example in attached file.