Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I recently posted a question on Averages which fixed my problem at the time. However, what I have found is that it does not work for some of my calculations, as it omits the zero records
I have a table that looks as follows:
Week_No | 1 | 2 | 3 | 4 | AVG
Employee ID | Employee Name |
1 Ben 2 2 3 3 2.5
2 Alan 2 2 0 2 2
What I am expecting to see, is that Alan's average should be: 6/4 = 1.5, not 6/3 = 2, so that it does not omit the zero values
The calculation I am using in this case is:
count({$<WAGE_CODE_NAME>} ACCOUNT_DATE / count(distinct WEEK_NO)
Week_No is being used as the dimension with 'Partial Sums' selected to provide the average
Many Thanks
Ben
In your table, the zero value really exist? Or it is created in the table?
Maybe you could change the expression a little bit, so all the employees have the same number of weeks as reference:
count({$<WAGE_CODE_NAME>} ACCOUNT_DATE / count(distinct WEEK_NO)count({$<WAGE_CODE_NAME>} ACCOUNT_DATE / count(distinct total WEEK_NO)
Hope this helps,
Erich
Hi Erich
Thanks for the reply
The zero value does not exist, they are null records. I suspect I need a way to create values, so it forces an entry on every week? Is there a way of doing this?
Many Thanks
Ben