While updating a straight table, I noticed an issue that relates to a Monthly Average that I have not been able to figure out.Our Month field is based on our facts table so we only have a month record if there was a dollar amount recorded for that month. So if we count the months to get an average for the first quarter, it only averages based on actual recorded months instead of the selected three months.I have tried to use the GetSelectedCount and the GetPossibleCount to work but they also have limitations and do not work in all instances.
I created a simple Qlikview app to demonstrate the issue.In the sample Jill only has two data points so when I select all three months her average(Average Possible ) is correct when we include John's data.As soon as we select 'Jill' the Possible count switches to 2 months and therefore does not include the average for the first quarter.
I was beginning to investigate the Min/Max values yesterday, but also ran into issues with selecting a name and not having the 'zero' values to count. Using Min/Max values would also limit the user to only be able to select consecutive months but that should be okay if I can get it to work.
I updated the data as shown...
When I select Jill in this case I only get 2 months in both expressions.