topic Re: Using distinct with Sum in set analysis in App Development

Using distinct with Sum in set analysis

lailarhc — Wed, 23 Jun 2021 12:23:48 GMT

Hello.
I need to know the sum of Hours (horas) of the distinct values of a dimension (Curso).
Here's what my expression for the KPI looks like:
=Sum({$ <Curso =- {''}>} Horas)

I thought I could use =Sum({$ <distinct Curso =- {''}>} Horas), but that's not allowed.

In the following image, the Kpi should display 44 instead of 88, as 2 + 10 + 16 + 8 + 8 = 44.

Here's another example:

It should display 181 as that is the result of 25 + 24 + 60 + 24 + 40 + 8

What am I doing wrong?
Thank you in advance.

Re: Using distinct with Sum in set analysis

sunny_talwar — Wed, 23 Jun 2021 12:27:25 GMT

Try this

Sum(Aggr( Sum(DISTINCT {$<Curso =- {''}>} Horas), Curso))

Re: Using distinct with Sum in set analysis

lailarhc — Wed, 23 Jun 2021 12:50:21 GMT

I thought it worked as the kpi was displaying the correct numbers, until I came across this:

I filtered it by year. It's the same course taken by four different employees. It should count as 8 * 4 hours, but it's only counting the course once.

Why is that?

And thank you for answering 🙂

Re: Using distinct with Sum in set analysis

sunny_talwar — Wed, 23 Jun 2021 12:53:52 GMT

Try this

Sum(Aggr( Sum(DISTINCT {$<Curso =- {''}>} Horas), Curso, Servidor, Ano))

Re: Using distinct with Sum in set analysis

lailarhc — Wed, 23 Jun 2021 13:17:52 GMT

It worked!
Thank you 🙂