Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ETL issue for average value

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

4 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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.


Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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.