YOY count

damen

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.

Any recommendations on the syntax?

MarkSnodgrass

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.

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

mhouston

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

MarkSnodgrass

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)

damen

@mhouston Yes

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

MarkSnodgrass

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

damen

@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

MarkSnodgrass

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.

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