Using Aggr with a time-based line chart

[ewindels]

Hello,

I have a table containing 3 columns: company, salesman, date
Each entry gives a time at which a salesman sold something to a company

I want to create a line chart which displays the median number of unique salesmen per company at each quarter

I'm currently doing the following:

Dimension: 

[date.autoCalendar.YearQuarter]

Measure:

Median(Aggr(count(distinct salesman),company))

However this is not working: The height of the curve is incorrect and there are values missing (see attached chart).
For instance, filtering the data by quarter using a filter pane gives a different result (see second chart, the value for 2017-Q4 on the non-filtered chart is approximately 10, while it is around 3 on the filtered chart, which is actually the correct value)

Am I doing something wrong? Thank you for your help

Non-filtered chartFiltered-chart 2017-Q4

[stevejoyce · Accepted solution]

if you have a company in multiple yearquarter, you need to add yearquarter to your aggregation, otherwise the count of salesman will only be available to 1 of the yearquarter.

Median(Aggr(count(distinct salesman),[date.autoCalendar.YearQuarter], company))

[ewindels]

That worked, thank you very much!