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!
Hi @DylanM! Apologies for delay - I just needed some time to circle back to this issue.
It’s not based on a parameter, its just a simple multi-value select filter for the BD Product Code.
I tried replacing all my calculations with the Lac-W calculation you mention above, however it instead provides a YoY Growth Calculation of -93% for all rows.
I went ahead and experimented with changing the syntax (e.g. added in customer name as my partition by attribute) but it generates the same results
Hi @Xclipse - thanks for checking in. Appreciated Dylan’s response but unfortunately it didn’t resolve the issue.
Thanks @cmot, please can you 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
Hello @cmot, how are the values returning for the 2024 YTD Rev and the 2023 YTD Rev calculations that I wrote above? Are those returning values that make sense before putting them in the YoY calculation?
Hello @cmot, since we have not heard back from you with any further information, I will go ahead and archive this topic. If you need further assistance with this issue, please post a new question in the community and attach a link to this one to provide relevant information. That will ensure you are at the top of the priority list for a response from one of our QuickSight experts. Thank you!