Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: ETL issue for average value

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable

Re: ETL issue for average value

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.


Re: ETL issue for average value

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

Re: ETL issue for average value

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.

Community Browser