2 Replies Latest reply: Mar 21, 2013 8:20 AM by Mikael Gerhardsson

# Average of column within the same quarter

Hi!

I need to calculate an average value of a column [Cost]. My reports are excelfiles which have the following structure:

DateCost
2012-01-31aaa
2012-01-31

bbb

2012-01-31ccc
2012-01-31ddd
2012-01-31eee

Date are always the last date of a month and there are one file for each month, so averaging over the column is not a problem with avg(Cost). I have imported all these files into a table in qlikview.

My problem is that I need a formula for calculating the average cost within the same quarter (up to that date)...Example:

 Date Cost 2012-01-31 1 2012-01-31 1 2012-01-31 1 2012-02-28 2 2012-02-28 2 2012-02-28 2 2012-03-31 3 2012-03-31 3 2012-03-31 3 2012-04-30 4 2012-04-30 4 2012-04-30 4

The formula should show:

January: avg(Cost) = 1+1+1/3 = 1

February: avg(Cost) = 1+1+1+2+2+2 / 6 = 1.5

March: avg(Cost) = 1+1+1+2+2+2+3+3+3 / 9 = 2

April: avg(Cost) = 4+4+4/3 = 4

I have tried load all data from above table into a new one and add a new column for which Quarter it is: load .. 'Q'&Ceil(Date/3) & '-' & Year(Date) as QY and then use: (not correct QV syntax here)

avg({<QuarterYear >= {QY}>} Cost)

But this depends on that I select a Quarter... I am kind of stuck, can someone give me some hints on how I should think about this problem?

Best regards

Mikael

• ###### Re: Average of column within the same quarter

See attached qvw.

• ###### Re: Average of column within the same quarter

Thank you Gysbert! That works.

Unfortunately the requirements was missunderstod and what we need is now is only the average with in the same quarter. Which means if Jan, Feb and March data is available then the formula should calculate the average of these month and show the same value for each month.

Example:

If all months in a specific quarter data is available:

formula calculates same result for each month wich is average of cost for these months.

Jan: (sum of cost for every month in this quarter)/count

Feb: same as Jan

March:Same as Jan

If only we have the first month in a quarter of data available:

formula calculates avg of one month.

If only data for two months the formula needs to calculate same result for these two which is the average of them.

Hope you understand what I mean... I am sure your code can be modified in an easy way...

Best regards and Thanks.

/Mikael