LAC / Aggregating Strings in tables (find top/bottom columns)

Question & Answer
, ,
1

Hello Team!

I have the following dataset:

image

I need to create one column named “Top shop” that will display the name of the top shop per region and “Bottom shop” that will find the bottom shop.

This table should look like that:

image
image718×192 9.08 KB

I should also be able to filter it:

image
image902×118 9.25 KB

I managed to find Top and Bottom values ( max(Value int,[Region]) ) but I don’t know how to display the name of the corresponding shop.

Is it doable? The way I see it requires me to compare two values - Top/Bottom and Value and display the name. I can’t aggregate strings, so I see no way around it for now.

image

Maybe there is another solution for this in QS?

Dashboard below:
Simple example

Thanks!

2

Hi @Filo

Do you want to filter by shops and perform Top and Bottom value check ?

If you want to compare with max or min value across the dataset, you can try below calcs

Top Value : maxOver({Value_Int},[Region],PRE_FILTER)
Bottom Value : minOver({Value_Int},[Region],PRE_FILTER)

and compare these top and bottom with Value Int to print shop.

Let us know if this works for your scenario ?

3

Hello @Ashok !

Thanks a lot for reaching out :slight_smile:

This is a step into right direction, I didn’t even realize that by default LAC will get filtered.

This is the result I have now:

image
Top Value: maxOver({Value_Int},[Region],PRE_FILTER)
Value check: ifelse({Value int}={Top value pre filter},Shop,NULL)

Now, when I see the whole table, I know that top Shop is C. When I filter down to shop C, I will also know that this is the top Shop. However, ideally, I would like to see this info in the whole table, like in the picture no 2 in the original post.

Now, when I filter down to shop B I only know that this is not the top Shop. I’d like to know which shop is the top one:

Current state:
image

Ideal state:
image

I know that top Value is 12, so this is Shop C, but I don’t know how to transfer it into a string without aggregating it.

(also, there could be more than one top Shops, but this is further down the line :slight_smile: )

Thanks a lot!

4

Hi @Filo. Checking in. Is this issue resolved? Let me know if I need to deep dive into the problem. In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena

5

Hello @Xclipse !

Thanks for reaching out!

No, this is still not resolved.

[The dashboard is here](Simple example)

Summing up the challenge here:
A. I want to know which shop is the top one (C, 12)
B. I want to create a column that will be populated with the top shop (“C” from top to bottom)
C. … so that when I filter to B, I still know that it is C that it the top one with value of 12
D. I am curious how QS will handle the case, when this more than one shop with Top value

So far I managed to achieve A. Column B is not ideal because “C” is only in “C” row. I understand from my experience with other tools that string should be aggregated and I can’t achieve this in QS

Thanks a lot! :slight_smile:

6

Hi @Filo, I think the straight forward solution it at the SQL layer:

WITH RankedShops AS (
    SELECT
        Region,
        Shop,
        Value,
        MAX(Value) OVER (PARTITION BY Region) AS Top_Value_per_Region,
        MIN(Value) OVER (PARTITION BY Region) AS Bottom_Value_per_Region,
        RANK() OVER (PARTITION BY Region ORDER BY Value DESC) AS Rank_Desc,
        RANK() OVER (PARTITION BY Region ORDER BY Value ASC) AS Rank_Asc
    FROM
        Source
)

SELECT
    Region,
    Shop,
    Value,
    Top_Value_per_Region,
    (SELECT Shop FROM RankedShops RS2 WHERE RS2.Region = RS1.Region AND RS2.Rank_Desc = 1) AS Top_Shop,
    Bottom_Value_per_Region,
    (SELECT Shop FROM RankedShops RS3 WHERE RS3.Region = RS1.Region AND RS3.Rank_Asc = 1) AS Bottom_Shop
FROM
    RankedShops RS1
ORDER BY
    Region,
    Shop;

Here are some more useful resources:

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

7

Hi!

Thanks for reaching out! I appreciate :slight_smile:

Sorry, I might still be thinking in Tableau/PBI terms and did not explain correctly (or the one row example was not very fortunate), but calculating the value in advance (that’s how I understand the SQL solution) will not work in my example, as I understand the value could be dynamic.

For instance, this is the client that works with the whole dataset:

image
image1117×419 21 KB

This is the client that works only with a subset of data (A and B only - in Tableau that would be a context filter applied before calculations)

image
image1034×466 21 KB

Or, in other terms, can QS do the trick?

8

Hi @Filo, besides using SQL or multiple visuals stacked next to each other – I’m out of ideas. Sorry, currently this is not possible but I’m marking this for feature request. At AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service. I have tagged this as a feature request. More features are being added on a regular basis, so please keep an eye on the What’s New / Blog . You can set up a Watching Alert by clicking on the bell icon.