topic Re: Average Charges Per Day in QlikView

Average Charges Per Day

kroberts13 — Tue, 10 Jan 2017 13:24:01 GMT

I am trying to calculate average charges per day of the month. So for example I need to look back 12 months to get the average charges on each day. On the 1st, I need to calculate the average charges for the previous 11 months on the 1st. I need to do that every day. I need that to be rolling for the month. Meaning, If the average on the 1st of the month is $3,000 and the average of day 2 is $4,000. I need day 2 to show $7,000.

This is how charges are calculated.... Sum({$<[DETAIL_TYPE] = {1,10}>}AMOUNT)

Re: Average Charges Per Day

sunny_talwar — Tue, 10 Jan 2017 13:32:32 GMT

Would you be able to share few rows of data with the output you expect from it? or a sample?

Re: Average Charges Per Day

kroberts13 — Tue, 10 Jan 2017 13:35:05 GMT

PostDate	HistoricalChgs
1/1/2017	$7,847,408.83
1/2/2017	$5,232,729.55
1/3/2017	$7,877,031.72
1/4/2017	$10,548,212.53
1/5/2017	$13,286,156.79
1/6/2017	$16,188,595.88
1/7/2017	$19,458,974.48
1/8/2017	$23,025,127.71
1/9/2017	$25,805,087.64

Re: Average Charges Per Day

sunny_talwar — Tue, 10 Jan 2017 13:36:42 GMT

What is the expected output?

Re: Average Charges Per Day

kroberts13 — Tue, 10 Jan 2017 13:39:21 GMT

That is what I currently have in Excel, but it would be a lot of manual work to update the Excel file for Qlik every day. That is what I am trying to get in Qlik. IF it is possible to get charges (Sum({$<[DETAIL_TYPE] = {1,10}>}AMOUNT) averaged out for the specific day of each month looking at the last 12 months that would be great.