- Can i add row number in front of multiple measures. Each measure shows as a separate row in the QS pivot table.
- Is there a way only show last week WoW data in a weekly historical trend for each value like below in QS? If there is no straight forward way, I can also adjust the dataset. but the dataset should stay in lowest granularity level (no aggregation)
what if each row is a measure/calculation field not different value from same calculation, can I still do this?
Hello @yingkz, welcome to the QuickSight Community!
In regards to your first question, while there isn’t an exact way to add a row number, what we have often recommended to accomplish this is to use the rank() function in a calculated field to build your row numbers based on the way you are sorting your pivot table. I think it works a little better with a regular table but you should be able to implement that here!
As for the WoW, if you just want the difference between the 2 most recent weeks to show, maybe you just use the Lag function to bring in the value for the previous week, then run a calculation on the values for this week and last week to get the difference.
Let me know if that helps! Feel free to mark my response as the solution, or follow-up with any questions you may still have. Thank you!
Hello DylanM,
Thank you for the reply, but let me clarify my question:
About this first question:
I understood I can use Rank to add a row number but my question is how to add row number/rank for multiple measures. each measure is a separate calculation in quicksight.
for example:
Volume= count(id)
Amount= sum(amount)
What I want is:
Rank/Row Measure Value
1 Volume 100
2 Amount $500
About the 2nd question:
I understood I can use lag function to create WoW but my table is like the screenshot below, I need last xx week trend but for WoW I only need last 1 week WoW number. In the screenshot below I only filter for last week WoW in calculation but all the other weeks are still have blank empty column if I remove null value it will only show last 1 week historical trend.
Hello @yingkz, in regards to the first question, that is not possible. Maybe if you have your dataset formatted in a way where you had a MeasureType column for “Volume” and a MeasureValue column for 100, you might be able to create the rank function how you are expecting. If they are values on different columns though, it will not function how you are expecting. There is a possibility with an ifelse calculated field, where you determine whether volume or amount will be returned, but I am not certain how your dataset is set up. My initial thought is likely the best way to receive your expected solution.
As for the 2nd question, since this is a field you are adding as a column, it is going to show on each of the week sections even if it is null. You could use conditional formatting or use the More formatting options on that field to display the NULL values differently, but with the way your pivot table is built, I don’t think it can be altered to your exact specifications. The only other possible option I can think of, which I am not certain would work, would be to add the WoW field, hide it in the visual, and see if it will allow you to add a total or subtotal for that column and you could change the name.
Let me know if this helps. Feel free to mark my response as a solution or follow-up with any further questions you have. Thank you!
@DylanM , Thanks for the reply. Does quicksight have the availability to show row and column level Diff or Avg beside Total?
Hello @yingkz, technically yes, but it depends on how you are aggregating the field within the visual. You can utilize LAC-W functions to manipulate how the output of a total or subtotal will aggregate on a table, but there isn’t a direct visual option to change just the aggregation of the total. So it would have to be something that would aggregate each row in the table as well as the displayed total. Here is some documentation on the differences between LAC-A and LAC-W functions to give you a little more insight.
Thank you for the information @DylanM , my use case is I have each metric as a column in source dataset. I need to create WoW and trying to know I can create one WoW calculation for all the metrics in the value section below? Or I have to unpivot the data in the datasource?
Hello @yingkz, I suppose you could try using the toString function to convert the value to a string and enter it as a column value, but I am not sure how exactly that would display in the pivot table. With the way your pivot table is built though, I don’t see a path towards adding a field in one single column within 1 group, unless you find a way to manipulate the output of the total/subtotal column with a calculated field.
@DylanM I tried and it will show the value for that column like this:
Hello @yingkz, I honestly think if you want the WoW to appear how you are expecting, it can’t be a pivot table. If you do not want the WoW value to repeat for each week, you will have to switch the display to a table visual instead, then you can display only a single value to compare the last 2 weeks.
I know it isn’t your desired output, but that is the closest we will be able to get to your expected output. I will mark this as a solution, but if you have further questions, please let me know.
@DylanM I tried table visual too it seems like only can diplay in column or row, can not display each measure as a row and wow as a column. Here is something I tried
Hello @yingkz, my apologies for the delayed response. Based on the discussion we have had on this issue you are facing, I think it is likely that you will need to divert from the desired output of this visual. I know you do not want to display the WoW on every week group within the pivot table, but I think that is the closest we will get to your desired output. The only other alternative is to display the WoW value for a single week in a KPI that will display next to your table.
I’m sorry we couldn’t get you closer to the exact result you were looking for, but I believe those are the most suitable alternatives.
Thank you @DylanM. I create a seperate table just show WoW for last week next to the weekly historical trend
1 Like