topic Re: Average per Month Issue in QlikView

Average per Month Issue

mneidlinger — Mon, 23 Mar 2015 14:35:41 GMT

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.

Any help would be appreciated.

Re: Average per Month Issue

luciancotea — Mon, 23 Mar 2015 14:51:41 GMT

It works correctly. You just miss the 0 (zero) value for Feb. To have a correct average value, 0 values are important.

The problem is with the consistency of the data, not interface expression.

Re: Average per Month Issue

jonathandienst — Mon, 23 Mar 2015 14:56:31 GMT

Something like this might work:

Sum(Sales) / Count({<Employee=>} Month)

The set expression ignores employee selection, but does assume that every month has at least some sales for some employee.

HTH

Jonathan

Re: Average per Month Issue

mneidlinger — Mon, 23 Mar 2015 19:39:36 GMT

Thanks for the fast response.

I was hoping to avoid adding zeros since the actual data is several million rows with subcategories that would also have to be populated with zeros.

Any other thoughts?

Re: Average per Month Issue

mneidlinger — Mon, 23 Mar 2015 19:43:05 GMT

Thanks for the suggestion John, but it did not seem to work when I added the set analysis.

(No Current Selections)

Any Other Thoughts

Re: Average per Month Issue

jonathandienst — Tue, 24 Mar 2015 05:42:34 GMT

No, the set analysis will not work if NAME is a dimension, it only ignores selections. In this case, use

COUNT(Distinct TOTAL MONTH)

Max(MONTH) - Min(MONTH) + 1 // your months are duals, so you can do arithmetic on them

HTH

Jonathan

Re: Average per Month Issue

mneidlinger — Tue, 24 Mar 2015 12:51:54 GMT

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.

Re: Average per Month Issue

Wed, 08 Apr 2015 16:47:36 GMT

In order to count both Green and Gray selections of the field = Month, use:

GetSelectedCount(Month, true())

GetSelectedCount(fieldname , includeexcluded=true or false)

returns the number of selected values in the field named fieldname. If includeexcluded is stated as true, the count will include selected values which are excluded by selections in another field.