Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mneidlinger
New Contributor II

Average per Month Issue

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.

Tags (2)
7 Replies
luciancotea
Valued Contributor

Re: Average per Month Issue

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.

MVP
MVP

Re: Average per Month Issue

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mneidlinger
New Contributor II

Re: Average per Month Issue

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?

mneidlinger
New Contributor II

Re: Average per Month Issue

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

Average per Month.JPG

(No Current Selections)

Any Other Thoughts

MVP
MVP

Re: Average per Month Issue

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

     COUNT(Distinct TOTAL MONTH)

Or

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

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mneidlinger
New Contributor II

Re: Average per Month Issue

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...

Average per Month2.JPG

When I select Jill in this case I only get 2 months in both expressions.

Average per Month3.JPG

Not applicable

Re: Average per Month Issue

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.

Community Browser