Best mode - combine MTD and YTD

Hi there!

I have a problem with beast mode. I think the solution is easy, but I can´t figure out the correct function in beast mode.

We need to display sales of our team. Therefore I created a table with team names and sales MTD and Sales YTD.

My datasource includes a field called season (our business year is from May till April next year) which I use for my Sales YTD.

Unfortunately, I can´t figure out the MTD part, which is always based on the current month.

I tried this function in beast mode:

(CASE
 when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then 
 SUM((CASE when `Total` is null then 0 else `Total` end))
 else 0
 end)

In return I always get 0 per person. If I add "this month" in my card builder the function works, but my YTD doesn´t anymore.

Do I need to add something in my MTD formula, because I think I have an issue with identifying dates (maybe?).

I wasn´t able to find anything close in other thread.

Many thanks for helping me!

Find more posts tagged with

Accepted answers

ST_Superman

I would try pulling the aggregate out of the case statement.

sum(CASE
when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then
ifnull(`Total`,0)
else 0
end)

All comments

ST_Superman

I would try pulling the aggregate out of the case statement.

sum(CASE
when MONTH(`Datum`)=MONTH(CURRENT_DATE()) and YEAR(`Datum`)=YEAR(CURRENT_DATE()) then
ifnull(`Total`,0)
else 0
end)

unknown

Thank you for your help Superman!!

I don´t really understand why my function didn´t work out, but yours did!

ST_Superman

A case statement works through your data one row at a time. It looks for the first "when" statement that is correct and then evaluates the "then" expression. If no when statement is true then it goes to an "else" statement or returns a null value.

In your case, you had one when clause and an else statment. So, when it evaluated the expression, the first row of data did not meet the when clause (month and year) so it went ahead to the else statment and returned 0.

by pulling the aggregate sum() function outside of the case statement, you are telling the case statement to continue running for all rows of data and then sum all of the results.

Hope that helps. Let me know if you have more questions.

unknown

Thanks for helping me out!

I think I understand your solution.