Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

macuevah
New 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

Re: Count Distinct HELP!!!

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
New Contributor

Re: Count Distinct HELP!!!

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

Re: Count Distinct HELP!!!

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
Valued Contributor III

Re: Count Distinct HELP!!!

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

kaanerisen
Contributor III

Re: Count Distinct HELP!!!

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.

Community Browser