How to make the math right with variables as filter

Options
Thuy_Tu
Thuy_Tu Member
in Calculations & Variables (Beast Mode)

I'm using variable as the filter for a dashboard.

Screenshot 2024-02-29 at 3.13.44 pm.png

Basically I created variables named "timeframe" with values of Month, Quarter & Year

On the breakdown, it works perfectly with timeframe as dimension.

However, for the summary card, i used timeframe as the filter with the below SQL

Screenshot 2024-02-29 at 3.10.38 pm.png

Funny thing is if I pull out sum('Budget') or Sum('Actual') only, it shows the correct number (aka 558k for actual & 56k for budget for this quarter). But when I subtracted actual from budget, it shows -526k instead of the the correctly calculated number of -501k. Please see the screenshot.

Please help!

Thanks!

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    edited February 2024 Answer ✓

    Wrap your entire outer/first case statement in a SUM function and replace all SUM(`Budget`) with COALESCE(`Budget`,0). Do the same for Actual


    its doing the subtraction after all records have been added together instead of summing the differences for each record causing the discrepancy

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited February 2024 Answer ✓

    Wrap your entire outer/first case statement in a SUM function and replace all SUM(`Budget`) with COALESCE(`Budget`,0). Do the same for Actual


    its doing the subtraction after all records have been added together instead of summing the differences for each record causing the discrepancy

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Thuy_Tu
    Thuy_Tu Member

    Thanks so much! This works like a champ!

    I didn't know that the Coalesce function can do the sum up!

Categories