How do i assign specific serial numbers to rows like the picture below?
Hi @Jane_Onwuchekwa ,
What is criteria to group a & b into index 1 likewise c & d to 2 ? If you have column which defines these grouping already then you can use rank function something like
rank([col1 ASC], [col2],PRE_FILTER)
I have created one approach for you to take a look.
SerialNo
You can copy analysis by clicking on the copy icon on left pane of this sample dashboard
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 )
Please let us know if this is helpful.
The columns have already being grouped into categories.
Field1 | Field2 | Category | serial no |
---|---|---|---|
a | aa | category1 | 1.01 |
b | bb | category2 | 2.02 |
c | cc | category1 | 1.02 |
d | dd | category3 | 3.01 |
e | ee | category2 | 2.02 |
f | ff | category3 | 3.02 |
g | gg | category4 | 4.02 |
h | hh | category1 | 1.03 |
There are table visuals created for each category. I want to create a sequential serial number like below.
category1 | category2 | ||||||||
---|---|---|---|---|---|---|---|---|---|
Field1 | Field2 | Category | serial no | Field1 | Field2 | category | serial no | ||
a | aa | category1 | 1.01 | b | cc | category1 | 2.01 | ||
c | cc | category2 | 1.02 | e | ee | category3 | 2.02 |
Hello @Jane_Onwuchekwa , thanks for the clarification.
Please notice that assigning serials that groups rows based on a certain column value (category in this case) would be simpler to do on the data backend, for example several backends support the NTILE operation that would allow you to group rows based on categories and then use the group field to generate the serial, where the group would be the first part.
Doing this on the BI layer wont be possible as it requires going through the different rows (grouping by the Category), QuickSight has table calculation functions that you can use to create ranks and groups but they operate only with numerical fields not literals (the category in this case) .
Hope it helps, please mark this solution as solved if that’s the case also to help other members of the community., otherwise let us know.
Thank you for posting into the QS community!