Table group by one column, display first value of others

Question & Answer
, ,
1

Hi there,

I am looking to do something rather simple but for which I don’t seem to find an answer for.
I have a table which contains orders with turnover. Each row has a {client name}, a {client vertical} and a {turnover} column.
I want to group my data by {client name}, take the first value of {client vertical} linked to that client, and sum the {turnover} for each row.
The thing is that if I put {client vertical} in the values section, then the only operation I can do on it is “count”, which is not what I want, and if I put it in the group by section, then it will group on it, and I don’t want that: I want one row per {client name}, I don’t want multiple rows if there are multiple verticals linked to the same {client name}.

I have tried to create a calculated field with the firstValue function as so:

firstValue({client vertical}, [{client name} ASC], [{client name}])

That gives me the error “VISUAL_CALC_REFERENCE_MISSING”. To fix the error, I have to put the {client vertical} in the grouped section, and then I do have what I want in that field, but the data is then grouped by {client vertical}, which is again, not what I want.

Any idea?

2

Hi @alejosne and welcome to the QuickSight community!
So, just to confirm; you’d like to add up the turnover’s for only the first value of each client vertical is that correct? And are those being added up per client name as well?
So basically: add up the turnover of each client vertical per client name or per combine all client names in that sum?

Another idea you could potentially try out; use the denserank calculation for client vertical so that you can obtain the first value. (you can add to your table and hide it as to not add unneeded columns).

Then you can use an ifelse statement in your sum calculation to only include rankings that equal ‘1’.

Let me know if you have any additional questions!

3

Hi Brett,

Thank you for your answer. I will try to see if denseRank does the trick.
So basically, I have the following data

row 1:
Client: MyClient
Vertical: MyVert
Turnover: 10

row 2:
Client MyClient
Vertical: MyOtherVert
Turnover 20

And I want to have a table with:

Client: MyClient (group by client)
Vertical: MyVert (first value of vertical)
Turnover: 30 (sum of turnover)

Thx,
Arnaud

4

So I have tried the denseRank solution but it does not do the trick as I don’t want to just remove the rows and lose their trurnover, I want to be able to sum it up.

5

Hi @alejosne,
Thanks for the additional explanation! By the sound of this, the maxOver calculation may be able to provide you with the desired outcome. Check out the documentation included below and let me know if this works for your case!

1 Like
6

Hi @alejosne,
It’s been awhile since we last heard from you so checking back in to see if you had any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

7

Hi @alejosne,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!