Hi everyone, Beginner here and loving GoodData! I’m struggling with one thing though, even after lots of Googling and theoretically finding the answer to my prayers… One of the goals that our business monitors doesn’t occur every week (it’s a high-value event). This means that when I view a Column Chart viewed by week, I don’t see the weeks displayed where zero of these goals occurred even though I would like to. I think that the simple solution should be to create a metric that wraps the COUNT in an IFNULL statement. The problem is that when I do this, I get all weeks returned from 1900 until 2050, even when the filter is set to “Last 90 days”. It does however set the weeks that have none of the events to zero! So it kinda works, but I’m obviously doing something incorrectly… The metric I’m using is this: SELECT IFNULL(COUNT(xxxxxxx),0)*1 Any ideas on what I’m doing incorrectly would be super welcome!

Hi Simon, Thank you for your question and glad to hear you are loving GoodData! Can you slightly modify your metric syntax as follow:

SELECT IFNULL(SELECT COUNT(xxxxxxx),0)*1

Let me know if that does the trick.

Thank you for your quick response! Unfortunately that doesn’t work - it returns an error and won’t let me save the metric, saying:

Unexpected 'SELECT'. Expecting: REPORT, PERCENTILE, -, FUNCNAME12, STRING, RANKFUNCNAME, IDENTIFIER, NATURAL, OBJECT, FUNCNAMEN, ZERO, (, REAL, SHORT_STRING, CASE, IF, PREVIOUS, IFNULL, FUNCNAME2, TEXT_ELEM, FUNCNAME1, COUNT, ELEM_IDENTIFIER, THIS, RUNFUNCNAME, NEXT

Hey Simon, I think the *1 in your original metric may be the issue. Removing it from the metric, may do the trick 🙂

My apologies, I tested your original metric syntax and it should return the desired data, however, you mentioned that you set the filter to “Last 90 days”, but the insight shows weeks. You need to make sure you are using the same date attribute in your filter as in your insight.

@Tomas Gabik Thanks for the idea - unfortunately it’s still looking the same (zero weeks not displayed)

This is currently limitation of the trick with IFNULL(x,0)*1 that it can fill the items without any data only if the report is sliced by only one attribute (date in your case). The trick works in a way that constant 1 is returned for all selected periods and it is multiplied by value of x or 0 if value x does not exist. All items will be displayed as well if you place another metric to the same insight which contains value in all periods together with metric x or ifnull(x,0). The problem with stacking is that constant 1 would have to be displayed not only for all weeks but for all UTM Campaigns (cross join of both dimensions) and query engine does not allow this cross join to happen. It is probably also not what you are expecting (you want to see all weeks but not all historical UTM Campaigns with zeros. I suggest to do it in following way • M1 is a metric which counts some business events which do not occur in all weeks for UTM campaigns. It is the COUNT(xxxxxxx) metric. • M2 is a metric which returns 1 in all weeks for UTM campaigns which shall be displayed. Can you count for example number of goals set for the given week of UTM campaign? • ifnull(M1,0)*M2 will return value of M1 or 0 in all the weeks

🎉 New note created.

@Jakub Sterba thank you very much for the advice. I’ve made the first metric successfully: SELECT COUNT(successfulgoal) WHERE successfulgoal <> (empty value) (the last <> is just to filter out irrelevant stuff) But I’m afraid I don’t know what you mean with:

M2 is a metric which returns 1 in all weeks for UTM campaigns which shall be displayed. Can you count for example number of goals set for the given week of UTM campaign?

For us UTM Campaign is just a value that goes alongside the user in a users table that allows us to see how the user first found our website. (The user can then complete this valuable goal on our website) Thanks :)