
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Your sample has a bunch of pivot tables. I can't see which one needs what....
Jonathan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Pivot table will calculate row basis....try something like this.
sum( )/aggr(NODISTINCT Count(DISTINCT Month),YEAR)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try this expression
=((suM([Sales (units)]) + sum(SOH)) /((suM([Rebate Units]) /Count(TOTAL MonthYear))))* 4
Regards,
Jagan.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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é

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try like
Sum(Total<MonthYear> [Rebate Units])
Regards
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
