Using Aggr with a time-based line chart

ewindels — Thu, 07 Oct 2021 14:41:35 GMT

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

Re: Using Aggr with a time-based line chart

stevejoyce — Thu, 07 Oct 2021 18:16:03 GMT

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))

Re: Using Aggr with a time-based line chart

ewindels — Fri, 08 Oct 2021 08:19:26 GMT

That worked, thank you very much!

topic Using Aggr with a time-based line chart in App Development

Using Aggr with a time-based line chart

Re: Using Aggr with a time-based line chart

Re: Using Aggr with a time-based line chart