Sorting using Calculated Field

[lbl]

Field, Product Category has values “Cookware”, “Others”, “Personal Care”.

In order to sort Product Category to show “Others” as the last item, calculated field productCatSort is created as “ifelse({Product Category}=‘Others’,‘ZZZ’,{Product Category})”.

However, using productCatSort as Off-visual field for sorting, “Min” is not available for Aggregation (only Count and Count distinct are available).

Does this mean that I need to have productCatSort as a dataset field which will increase the dataset size?

Thanks!

[Giridhar.Prabhu]

Hi @lbl

Please check if this helps. This is on a Pivot-Table. I am not sure which visual you are using this on. If this does not help you need to explore further.

Off-Visual sorting has only the Count and Count Distinct. But by including your sort column in the visual gives you the ability to sort by dimensions values.

Sorting Pivot Table Columns - Unable to Sort by Query Field

Hi everyone I have a dataset that I am building a pivot table over. I have a field containing a text value that will become the column headings when added to the pivot table. I would like to sort these columns left to right in a specific order and not just alphabetically by their text. So in my underlying query I built a numeric column that I was hoping to use to sort the columns by. It contains a value determined by a data pattern, but ends up like 101, 201, 302, 507, etc. I built the pivot t…

Regards,
Giri

[prantika_sinha]

Hi @lbl ,

Can you try encoding as numbers or integers? for example instead of ‘zzz’ attribute it to be 1000 and for the rest rank based on the field itself.

Check how the value varies for each field before applying the sort.

Thanks,
Prantika

[lbl]

hi @Giridhar.Prabhu
I am using Vertical Bar Chart. X-axis is Product Category, values is sum(Sales). Any idea how to add productCatSort to facilitate sorting and at the same time, not showing it?

Thanks.

[lbl]

hi @prantika_sinha
The field itself is String e.g. Product Category. It is therefore not possible to convert productCatSort to integer.

Thanks.

[Giridhar.Prabhu]

Hi @lbl

I reviewed this and saw that the hide option is not available in the Vertical Bar graph. Unfortunately, the off-visual field sorting is going to sort as a measure but your intention is to sort based on the alphabetical order.

You could for now use your calculated field and instead of ZZZ try “_Others” as the value and use that field in the Vertical Bar Chart. So it should show as the last value and will not be an eye sore. Its at best a work around.

Regards,
Giri

[prantika_sinha]

I understand it is string, but if we rank this based on the same field, we can generate a integer calculated field and use that up. I am not sure about your dataset, but can think of trying something like

ifelse(product category = ‘Others’, 4000,
rank(product category asc)

assuming unique product category counts to less than 4000 (considering the visual supports 4000 data points at a time)

And while sorting set the aggregation as no aggregation or average to get the same value reflecting.

[lbl]

hi Prantika
The formula is giving the below error.

[Giridhar.Prabhu]

Hi @lbl

Try the following (change fields per your dataset). This should work

Root cause for your error is that calculated field had both a single value (999) and aggregated value (from rank).

Refer article below for more details

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

Regards,
Giri

[lbl]

rank([ifelse(city = “Paris”, “ZZZ”, city) ASC]) does not give error.
Unfortunately, this cannot be used as sort field under Vertical Bar Chart X-axis.

Will adopt your workaround to use “_Others” as value.

Thanks.

[Giridhar.Prabhu]

Hi @lbl

It seems it is working with the Rank calculated field for my simulated case. See below

Regards,
Giri

[lbl]

hi @Giridhar.Prabhu

Error happens when Small Multiples is being specified.

[Giridhar.Prabhu]

Can you give a screenshot of the error?

[lbl]

hi @Giridhar.Prabhu
Below is the error. Thanks!

[Giridhar.Prabhu]

Hi @lbl

I presume you already solved this issue, but if not I think the issue is that the additional dimension you brought into the visual was not factored in the rank.

My example below has Region from the small multiples. You need to change it to the specific dimension in your case