Conditional statement in metric don't work properly in Gooddata report

Question

I have this metric:

The first value right above the "if" statement should be considered just for dates after September 1st;
The second value right above the "else" statement should be considered just for old dates (before September 1st).

What happens is that when I put the "hour" attribute inside the report, the value above the "if" statement is considered even for old dates.

The left report uses the same metric but without the hour attribute:

Why this happen? Can I do something to bring the right values?

OBS: I also tried to use the "case" statement in place of the "if" but I get the same result.

score 0 · Answer 1 · answered Sep 15 '18 at 16:07

0

You have to put the aggregation function (SUM in this case) around the CASE statement, not inside it. The reason is that you probably want to evaluate the condition on the fact table, not on the resulting aggregation function.

answered Sep 15 '18 at 16:07

David Kubecka

1
1

Thanks for the tip, but I made your sugested changes and it didn't worked (I removed the aggregation function from the subqueries and added it around the IF) – Alvaro Prataviera Filho Sep 16 '18 at 00:11
What exactly do you mean by "it didn't worked"? Can you share what are your expectations and what the current wrong results? – David Kubecka Sep 17 '18 at 12:43
I made your sugested changes and the results kept showing the wrong numbers as I showed in the image. – Alvaro Prataviera Filho Oct 02 '18 at 18:27

score 0 · Answer 2 · answered Oct 02 '18 at 21:27

I solved this up by removing the "if" clausule (which don't seems to work with many filters in the report) and including the sum of 2 metrics containing in each of they the respective date filter:

New metric using WHERE rather than IF

This solved my problem for now, but I'm still looking for new ways to make this metric for performance reasons, so if anyone has a new guess, I'll be happy to test it.

Conditional statement in metric don't work properly in Gooddata report

2 Answers2