How do I add links to the cells of a pivot table based on a condition?

[ShayShay]

The pivot table shows the count of ice cream stores by city and country. I want to attach a link to each cell that redirects them to the cheapest ice cream store by those conditions. For example, for Berlin in row and Germany in column, there are let’s say 200 ice cream stores, which is what the cell displays. But when I click that cell, it redirects me to the link with the lowest average price.

An example of the dataset that this pivot table uses is like:

Country, City, Store Name, Store Website, Average Price
Germany, Berlin, name1, Website a, 2
Germany, Berlin, name2, Website b, 3
Germany, Hamburg, name3, Website c, 2
Canada, Toronto, name4, Website d, 5

[Max]

Are you saying that the first two rows should be grouped together?

To get the lowest price per group of county / city you would need to do a min over.

lowest_price_per_country_city = minOver(avg(price),[{country},{city}])

Then you can check to see if the store is the lowest price.

is_store_lowest = ifelse(lowest_price_per_country_city=avg({price}),‘Lowest’,‘Not Lowest’)

Then you can filter your table to just provide the lowest stores.

Here is an example using client, username, device model and count of sessions, instead of country, city, store, and price.

You can then filter to your newly calculated field (you’ll need to do a custom filter and set it to “Lowest” or whatever you name the return value)

Finally, just to confirm you have an https link for your field?

You can then add an action that’s a url action and link the website as a field.

Let me know if that helps.

[ShayShay]

Hey @Max,

Thanks for your reply not what I asked for, but I still learnt a lot of new things from this. I’m sorry, but I can only post one picture in a post in QS Community because I’m new, but I took a screenshot of my longer post .

Would you know how to go about this?

Thanks!