Comparative Gage - What's wrong with my query?

leeloo_dallas
leeloo_dallas Member
in Charting

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?

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • leeloo_dallas
    leeloo_dallas Member

    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.

  • leeloo_dallas
    leeloo_dallas Member

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

Categories