topic Re: Calculate Monthly Average in QlikView

Calculate Monthly Average

Anonymous — Fri, 05 Jun 2015 13:59:57 GMT

Hi Everyone -

I have a table which lists a date and number of transactions for the last 3 months.

I am trying to add a third column called "Average Count Per Month" to show the average # of transactions per month. I would like to list the average number per month for each line so that I can create a graph similar to the one below (done through Excel).

I've tried using aggr() and sum()/count() a few different ways but no luck so far. Any idea how this can be done?

Thanks in advance.

Michael

Date	Count	Average Count Per Month
2015-03-11	3	4.33
2015-03-12	6	4.33
2015-03-13	4	4.33
2015-04-05	7	3.67
2015-04-06	2	3.67
2015-04-08	2	3.67
2015-05-12	4	4.00
2015-05-13	5	4.00
2015-05-14	3	4.00
2015-06-01	6	7.67
2015-06-02	8	7.67
2015-06-03	9	7.67

Re: Calculate Monthly Average

Fri, 05 Jun 2015 14:05:13 GMT

try to make a pivot and group by Dates and use Average function at count.

then do a chart on pivot

Hope It will solve.

Thanks

Re: Calculate Monthly Average

tschullo — Fri, 05 Jun 2015 14:23:35 GMT

You could add a month column, then add a table that contains the average for a given monthh that would be linked by month field. You could then graph that value in your line graph as well.

Table 1

Date	Count	Month
2015-03-11	3	Mar
2015-03-12	6	Mar
2015-03-13	4	Mar
2015-04-05	7	Apr
2015-04-06	2	Apr
2015-04-08	2	Apr
2015-05-12	4	May
2015-05-13	5	May
2015-05-14	3	May
2015-06-01	6	Jun
2015-06-02	8	Jun
2015-06-03	9	Jun

Table 2

Month	Average Count Per Month
Mar	4.33
Apr	3.67
May	4.00
Jun	7.67

Re: Calculate Monthly Average

tschullo — Fri, 05 Jun 2015 14:58:19 GMT

Script Sample:

DataTable:

LOAD * INLINE [
Date, Count, Month
2015-03-11, 3, Mar
2015-03-12, 6, Mar
2015-03-13, 4, Mar
2015-04-05, 7, Apr
2015-04-06, 2, Apr
2015-04-08, 2, Apr
2015-05-12, 4, May
2015-05-13, 5, May
2015-05-14, 3, May
2015-06-01, 6, Jun
2015-06-02, 8, Jun
2015-06-03, 9, Jun
];

MonthAverage:
Load
Month,
round(Avg(Count),0.01) as Average
Resident DataTable
Group by Month;

Re: Calculate Monthly Average

awhitfield — Fri, 05 Jun 2015 15:07:06 GMT

HI Michael,

see the attached example, looks pretty close:

Re: Calculate Monthly Average

Anonymous — Fri, 05 Jun 2015 18:47:21 GMT

Thanks Tony. This did the trick.

Michael