Bottom 10 Visual Showing Customers from Other Years Despite SelectedYear Parameter Logic in QuickSight

Q&A
,
1

We have a report called Parent Customer Performance, which includes the following visuals:

  • Top Mover – Customer Performers (Top 10)

  • Bottom Mover – Customer Performers (Bottom 10)

We are currently using the calculation below to display sales based on the selected year:

ifelse(
    {TRANSACTION_YEAR} = ifelse(${SelectedYear} = 0, extract('YYYY', now()), ${SelectedYear}),
    {DAILY_SALES},
    0
)

The problem is that some customers with 0 sales are still appearing in the Bottom 10 visual, even though they do not belong to the selected year. These customers are from different years, but because the formula assigns them a value of 0, they show up in the Bottom 10 ranking.

We cannot apply a direct filter on the year because the report needs to display both current-year and prior-year sales, which is why we are using the SelectedYear parameter. The logic works correctly for the Top Mover (Top 10) visual, but does not behave as expected for the Bottom Mover (Bottom 10) visual.

Any guidance on how to prevent previous-year customers from appearing in the Bottom 10 visual would be greatly appreciated.

2

Hi @vishalbagad,

By any chance is there an accessible field for the year of each customer? The reason I ask is because what I think could work is adding that customer year to the visual, and adding a filter to only display customers if customer year is equal to selectedYear. Please let me know if this is feasible or if I am misunderstanding your use case at all!

3

bottom N
bottom N230×738 22.9 KB

bottom Result
bottom Result1453×565 109 KB

I’ve attached two screenshots for clarification. In the chart, last year’s results are still appearing under the current year’s sales, even though those customers are not part of the selected year. We only need the Bottom 10 customers for the current year.

I cross-checked the data in my database, and the results are correct there, but QuickSight is still showing partially last year’s records. I’ve also shared the calculation used.

Please review this issue and suggest a solution.

Thanks,
vishal

4

Hi @vishalbagad,

I appreciate the screenshots displaying the behavior. This is definitely an interesting occurrence, as your calculated field also seems to be correct. Could you please send me over an Arena view link of this analysis (with no personal data)? This will allow me to look more closely into what might be the issue here.

Thank you!

1 Like
5

hi @WLS-Luis,
Here is the view link for the analysis. The data used is dummy data. Please review it and let me know if you need anything else.

[Top Movers - Customer Performance (YTD)]
(https://us-east-1.quicksight.aws.amazon.com/sn/account/Amazon-QuickSight-BI/analyses/e1563952-cf44-47f9-8e28-817a98681688)

The expected result based on my concern is as follows:

PARENT CUSTOMER 2025 SALES 2025 GP 2025 GP%
ACTION PLUMBING HEATING & HVAC $46.00 $18.40 40.00%
BEST HAUL LLC C O D $0.00 $0.00
BUCKLEY CABLE CONSTRUCTION CO $0.00 $0.00
EDP TRANSPORT ($777.00) ($388.50) 50.00%
J P MASCARO ALLENTOWN $0.00 $0.00
J P MASCARO MT OLIVE $0.00 $0.00
SPENCER’S CHROME PARTS & SERVICE ($975.00) $0.00 0.00%
T J MCGEEHAN SALES & SERVICE $0.00 $0.00
WASTE MANAGEMENT GILBERTSVILLE $0.00 $0.00
WASTE MANAGEMENT TELFORD $0.00 $0.00
Total ($1,706.00) ($370.10) 21.69%

Thanks,
Vishal

6

@WLS-Luis & Team
Awaiting for your response on the above issue thread.

7

Hi @vishalbagad,

Thanks so much for sending that info over! However, I can’t access that link since it prompts me to switch to a different account. Can you upload that sample data to Arena and share that dashboard with me in this thread? Here is a run-down of how to do so and please let me know if you have any questions.

Thank you!

8

Hi @WLS-Luis ,

I’ve shared the analysis link created in ARENA Sandbox with the sample customer data, and it reflects the same issue explained earlier.

Customers who have no sales in the selected year are getting assigned a value of 0, which makes them appear in the Bottom 10. Since the report must show both current-year and prior-year sales, we cannot directly filter by year, and that’s why these zero-sale customers are still included.

Bottom 10 Visual Showing Customers from Other Years Despite SelectedYear Parameter Logic in QuickSight

Amazon Arena Sandbox: An embedded instance of the Quick Sight capability, within the Amazon Quick Community - What’s New - Amazon Quick Community

Thanks,
Vishal

9

@WLS-Luis ,

waiting for your response on above thread

Thanks!

10

Hi @vishalbagad,

Sorry if I’m misunderstanding anything, but I think the links in your last message may have been added by mistake. The first one points back to this thread and the second goes to the Arena tutorial. If you’re still running into trouble preparing the analysis to share, please let me know and I’d be happy to provide clear, step-by-step instructions!

11

Hi @vishalbagad,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

12

@WLS-Luis , @JacobR
We still haven’t received any solution. Please review the entire thread end to end and let us know if you find a resolution.

13

Hi @vishalbagad,

I’m not sure if you had a chance to see my most recent message, but I’m unfortunately limited in how much I can assist without the correct Arena link, as that’s needed to review your analysis in detail.

As mentioned earlier, I’d be more than happy to provide clear, step-by-step instructions to help you create and share the analysis properly.

Thank you!

14

Hi @vishalbagad,

Hope everything is well! Just wanted to check in one more time if you are still facing this issue and would like step-by-step instructions for the Arena link so that I can provide further assistance. Any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you!

15

Hi @vishalbagad

Since we have not heard back from you, I’ll go ahead and archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!