Bug Identified for QS Calculated Field

Hi all - Our team identified what appears to be a significant bug with QS - it is not correctly calculating YOY growth when inputted as a calculated field - has anyone else experienced this and know ways around it? We’ve raised a SIM but haven’t heard back yet

Hello @cmot,

Do you have some examples where this is not working? Maybe create an Arena dashboard and share with the community so we can all take a look?

Thanks!

1 Like

Hi @andres007 - I don’t believe I’m allowed to share as it contains confidential team data - I can describe the situation however - we created the YoY calculation (below) which works fine as long as no filters are in place - however when a filter is put in for a bd product code, the YoY calculation will show negative growth despite 2024 revenue being higher than 2023. Hope that provides enough information!

ifelse(

sum({2023 YTD Rev}) >= 0 AND sum({2024 Rev}) >= 0,(sum({2024 Rev})-sum({2023 YTD Rev}))/sum({2023 YTD Rev}),

sum({2023 YTD Rev}) < 0 AND sum({2024 Rev}) < 0,abs((sum({2024 Rev})-sum({2023 YTD Rev})))/sum({2023 YTD Rev}),

sum({2023 YTD Rev}) < 0 AND sum({2024 Rev}) > 0,((sum({2024 Rev})-sum({2023 YTD Rev}))/sum({2023 YTD Rev})*-1),

sum({2023 YTD Rev}) > 0 AND sum({2024 Rev}) < 0,(sum({2024 Rev})-sum({2023 YTD Rev}))/sum({2023 YTD Rev}),

NULL

)

Hi,

Can you share how are you calculatin the 2023 and 2024 revenue fields?

The unexpected result might be related to the way QuickSight calculates the agregations. Have you looked at this documentation for Level Aware Calculations?

Thanks @andres007 - The calculation for 2023 and 2024 revenue is:

2024 Rev:
ifelse((extract(‘YYYY’,{sales_revenue_month_start_date})=2024),{sales_revenue_dollar_amount},NULL)

2023 YTD Rev:
ifelse((extract(‘YYYY’,{sales_revenue_month_start_date})=2023) AND
(extract(‘MM’,{sales_revenue_month_start_date})<=2),
{sales_revenue_dollar_amount},NULL)

1 Like

Also thank you for recommending the LAC A function, unfortunately it returned the same result

1 Like

Hello @cmot, my thought is you will want to utilize LAC-W aggregations rather than LAC-A. Also, how are you applying the bp product code filter? Is it based on a parameter, and if so is it single value or multi value?

If you alter the 2023 YTD Rev and 2024 Rev calculated field to look like this:
2024 Rev = sumOver(ifelse((extract(‘YYYY’,{sales_revenue_month_start_date})=2024),{sales_revenue_dollar_amount},NULL), [], PRE_AGG)

2023 YTD Rev =

sumOver(ifelse((extract(‘YYYY’,{sales_revenue_month_start_date})=2023) AND
(extract(‘MM’,{sales_revenue_month_start_date})<=2),
{sales_revenue_dollar_amount},NULL), [], PRE_AGG)

Then alter you YoY calculation like this:

ifelse(

minOver({2023 YTD Rev}, [] PRE_AGG) >= 0 AND minOver({2024 Rev}, [], PRE_AGG) >= 0,(minOver({2024 Rev}, [] PRE_AGG)-minOver({2023 YTD Rev}, [] PRE_AGG))/minOver({2023 YTD Rev}, [] PRE_AGG),

minOver({2023 YTD Rev}, [] PRE_AGG) < 0 AND minOver({2024 Rev}, [] PRE_AGG) < 0,abs((minOver({2024 Rev}, [] PRE_AGG)-minOver({2023 YTD Rev}, [] PRE_AGG)))/minOver({2023 YTD Rev}, [] PRE_AGG),

minOver({2023 YTD Rev}, [] PRE_AGG) < 0 AND minOver({2024 Rev}, [] PRE_AGG) > 0,((minOver({2024 Rev}, [] PRE_AGG)-minOver({2023 YTD Rev}, [] PRE_AGG))/minOver({2023 YTD Rev}, [] PRE_AGG)*-1),

minOver({2023 YTD Rev}, [] PRE_AGG) > 0 AND minOver({2024 Rev}, [] PRE_AGG) < 0,(minOver({2024 Rev}, [] PRE_AGG)-minOver({2023 YTD Rev}, [] PRE_AGG))/minOver({2023 YTD Rev}, [] PRE_AGG),

NULL

)

Something to note, I am aggregating 2024 Rev and 2023 YTD Rev in the ifelse statement above with minOver because the LAC-W calculation will basically make that value appear on every row in your dataset. If you use sum here, it will cause it to be much larger than it is supposed to be. Instead you use min and only get a value from a single row.

Then depending on how you are handling the filtering, you may just want to add a check in the 2024 Rev and 2023 YTD Rev calculated fields for the matching bg product codes. I would hold on that though until you test with the above calculated fields. Let me know if this helps!

1 Like

Thanks @DylanM, this is a brilliant approach. @cmot, we hope this solution works for you. Let us know if this is resolved. And if it is, please help the community by marking the above answer as a “Solution.” (click the check box under the reply)

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

2 Likes

Hello @cmot, since we have not heard back from you, I will mark my last response as the solution. Let me know if you have any remaining questions on this issue. Thank you!