Hi!
I need to calculate an average value of a column [Cost]. My reports are excelfiles which have the following structure:
| Date | Cost |
|---|
| 2012-01-31 | aaa |
| 2012-01-31 | bbb |
| 2012-01-31 | ccc |
| 2012-01-31 | ddd |
| 2012-01-31 | eee |
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
