Comparative Gage - What's wrong with my query?

Using the Comparative Gauge,

I wanted to display our performance by whether the number of impressions have increased or decreased.

Only, when I use the following window functions, I get a blank card with a downward arrow.

Current Month:

sum(case when MONTH(`Created Time`) = MONTH(CURRENT_DATE()) then `Likes`+`Shares`+`Comments` end)

Previous Month:

sum(case when MONTH(DATE_ADD(`Created Time`, interval 1 month)) = MONTH(CURRENT_DATE()) then `Likes`+`Shares`+`Comments` end)


What I want to do is capture the sum of the previous month in a beastmode, then capture the sum of the current month. Only, the issue arises when I realize that the values will be smaller since the current month is not complete. So I would need to capture where we are at now in comparison to the previous month.


Is this possible?

Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔵

    If you are adding together multiple values and one of them is blank or null, it will not be able to add them all together and just produce a blank value. You would need to do this: IFNULL(likes,0) + IFNULL(shares,0) + IFNULL(comments,0) in order to add them together. If the fields aren't actually null, but blanks, you would need to do CASE statements around them as well to replace that with zero. I find it easier to clean these types of data issues in the ETL so then my beast mode is easier to read.

    Also, to deal with the the incomplete months, you can add the DAY function just like you did the MONTH function but say when it is <= instead of = to.

    Hope this helps.

  • Thanks for your input, I'll need to double check to see if i changed my blank null or blank values to 0.

    That may be a step I neglected to remember.

    I'll get back to you if this resolves my issue.

  • So there are nulls in my data but I still can't get it work.

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!