Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Calculate average per month based on the count of months

Hi there,

I'm trying to calculate an a !verage per month (= the sum of ... divided by number of months available) but it doesn't work. Anyone any suggestions what I'm doing wrong?

If (in a text box) I apply the calculation "count(distinct(Month))" it gives me the number "2" which is correct since there are only two months of data available. However, applying the same expression in a pivot table doesn't work.

Much appreciated if someone can guide me into the right direction.

Cheers!

Tags (2)
7 Replies
MVP
MVP

Re: Calculate average per month based on the count of months

Hi

Your sample has a bunch of pivot tables. I can't see which one needs what....

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Calculate average per month based on the count of months

Hi,

Pivot table will calculate row basis....try something like this.

sum(  )/aggr(NODISTINCT Count(DISTINCT Month),YEAR)

hrlinder
Honored Contributor

Re: Calculate average per month based on the count of months

in the Pivot table you have the months in columns.

if you divide by total you should get the rcorerect result

sum(sales)/Count(total distinct monthnumber)

Not applicable

Re: Calculate average per month based on the count of months

Thanks for thinking with me. Several of the proposals did not work and to clear things up, I've attached a simplified file which includes the individual steps to be taken.

MVP
MVP

Re: Calculate average per month based on the count of months

Hi,

Try this expression

=((suM([Sales (units)]) + sum(SOH)) /((suM([Rebate Units]) /Count(TOTAL MonthYear))))* 4

Regards,

Jagan.

Not applicable

Re: Calculate average per month based on the count of months

Hi Jagan,

The issue with the 'Count(Total MonthYear)' is similar to the 'Count(Distinct(Month)) as it doesn't split the sum of Rebate Units by this number. In the solution you've provided, it will split the Rebates (per month) by 7 whereas the correct calculation should be the sum of Rebate Units (for the months measured) divided by 2.

Regards,

René

Re: Calculate average per month based on the count of months

Hi,

Try like

Sum(Total<MonthYear> [Rebate Units])

Regards

Regards,
Prashant Sangle
Community Browser