Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
macuevah
Contributor
Contributor

Count Distinct HELP!!!

Hello!!

I'm trying to use the following expression to express the sales avg considering the number of months.

Example:

Sum Sales in last 11 years = 654,229,440

Months for the period of time selected = ex: 11 years = 132 months (I have an expresión called Year-Month for every sales month "2017-01-Jan","2017-02-Feb" etc)

My expression is: =Sum(Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))

If there's no selection the expression works

654,229,440 / 132 = average sales per month

But when I select one specific year or more it still divides sales by 132 months, considering that if I select 1 year the formula should be:

654,229,440 / 12 = average sales per month


Or if I select 2 years:

654,229,440 / 24 = average sales per month


And so on, what am I doing wrong?


5 Replies
sunny_talwar

Are you sure the denominator is not changing? I think it seems that you don't want the numerator to change with selection? May be this

=Sum({$<[Year]>} Subt)/(Count(DISTINCT{$<[Year]-={'2006'}>} [Year-Month]))

macuevah
Contributor
Contributor
Author

I Think it's easier to look at if I only focus en the second part of the expression:

=(Count(DISTINCT{$<[Year]-={'2006'}>}[Year-Month]))


I have 11 years info, so no filters that is equal to 132, but if I select 2017 that should be equal to 12, but It's still 132

sunny_talwar

Unless your data model is not well connected, I don't see why Count of YearMonth field won't drill down on selection of Year... would you be able to share a sample where you can show the issue?

pradosh_thakur
Master II
Master II

does it change when you not use single quotes in '2006' and just 2006. The expression should work.

The only possible explanation i can think of which may forced you to think its not working is if all the months have same number of sales and the average will always be same. and you are assuming that the numerator will not change so that may be the case. but that is highly unlikely

Learning never stops.
kaanerisen
Creator III
Creator III

Hi,

If i understand your need correctly, you can use aggr for this.

Avg(Aggr(Sum(Subt),[Year-Month]))


This will show you the average monthly sales based on your selections.