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
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:
Using Date Dimensions for Reporting
This work around will take a second to set up but should allow you to see the results you are after. If you have any questions please let us know.
Kind Regards,
Dustin
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:
Using Date Dimensions for Reporting
This work around will take a second to set up but should allow you to see the results you are after. If you have any questions please let us know.
Kind Regards,
Dustin