Bug Identified for QS Calculated Field

Question & Answer
, , , , ,
1

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

2

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
3

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

)

4

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?

5

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)

6

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