YOY count

damen
damen Contributor
in Beast Mode

Hi all,

I am trying to get the loan count of this year / the loan count of last year and for some reason, I cannot get an output - here is what i have tried so far.

image.png

Any recommendations on the syntax?

If this helps, feel free to agree, accept or awesome it!

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Those period-over-period cards are kind of different in how they function. I believe your summary number isn't working as you want because your date range filter is set to this year. That keeps anything that would be in the denominator portion of your formula from being counted since that is looking for the previous year. Meanwhile, the bar chart portion of the period-over-period cards need you to set the date range the way you did.

    A couple options come to mind. Instead of using the summary number, you can use the multi-value gauge and it won't require you to build a beast mode. You can set the date range filter to graph by year and then configure the chart properties to show the percent change between the last 2 years.

    image.png


    Another option is to use a standard bar chart and built the year series as a beast mode. I walk through how to do that here.

    https://youtu.be/mDwSQGAjIrs


    **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.

Answers

  • mhouston
    mhouston Contributor

    @damen are you trying to do this in a beast mode?

  • MarkSnodgrass
    MarkSnodgrass Coach
    edited November 2022

    Try doing this as a different way to count without using the count aggregation inside the sum aggregation.

    SUM(CASE WHEN YEAR(`time_stamp`) = YEAR(CURRENT_DATE()) THEN 1 ELSE 0 END)
/
SUM(CASE WHEN YEAR(`time_stamp`) = YEAR(CURRENT_DATE())-1 THEN 1 ELSE 0 END)


    **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.
  • damen
    damen Contributor

    @mhouston Yes

    @MarkSnodgrass So I did try that as well and I'm still not getting any data in the output

    image.png


    image.png


    If this helps, feel free to agree, accept or awesome it!

  • MarkSnodgrass
    MarkSnodgrass Coach

    @damen what is your date range filter set to? do you have any other filters in place?

    **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.
  • damen
    damen Contributor

    @MarkSnodgrass Ok so I am creating a YOY chart showing the last three years work of loan count - I took the bar chart off to make sure that I was able to get an output first.

    here is the chart - I am really only trying to get the summary number to display the percentage of production we have achieved this CY vs last CY

    image.png


    If this helps, feel free to agree, accept or awesome it!

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Those period-over-period cards are kind of different in how they function. I believe your summary number isn't working as you want because your date range filter is set to this year. That keeps anything that would be in the denominator portion of your formula from being counted since that is looking for the previous year. Meanwhile, the bar chart portion of the period-over-period cards need you to set the date range the way you did.

    A couple options come to mind. Instead of using the summary number, you can use the multi-value gauge and it won't require you to build a beast mode. You can set the date range filter to graph by year and then configure the chart properties to show the percent change between the last 2 years.

    image.png


    Another option is to use a standard bar chart and built the year series as a beast mode. I walk through how to do that here.

    https://youtu.be/mDwSQGAjIrs


    **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.

Categories