Forum

how to get average value for each month

14 September, 2015

Hi,

I was trying to calculate the average BMI value for each month. So I created the hierarchy ( shown in the left part of image below) Then try to apply mean function and let it be grouped by month. But I couldn't find any dimension available here (shown in image below). Accordingly, the result of the report didn't group by month either.

Cathy

Hi Cathy,

Apologies for the delay in responding to this query! This post appears to have slipped through the cracks!

Unfortunately you cannot group an advanced function by a date field. The dimension you group by needs to be something other than a date (VARCHAR etc).

What you could do as a work around is add a date look up table to your data warehouse that associates month names to specific day dates. Then you could join that table in your view and use that month name field to group your advanced function.

If you are interested in this, here is a link to another forum post outlining date look up tables: