How to make the math right with variables as filter

Thuy_Tu

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

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

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!

GrantSmith

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

GrantSmith

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

Thuy_Tu

Thanks so much! This works like a champ!

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