Hi everyone, Beginner here and loving GoodData! I’...
# gd-beginners
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:
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:
Copy code
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)
@Moises Morales I think it’s kinda working, if I don’t “stack by” anything and then create a filter with a “relative period”. But if I try to “stack by” something it says “Sorry, we can’t display this insight”… (This is the background to what I’m trying to achieve:) Basically what I’m trying to see (at a glance) is which of our ad campaigns are driving this highly desired goal, week-by-week. If I create an insight report with this attribute selected, then filter out the values I don’t want to see, view by week, then stack by UTM Campaign, it works. I have it in my dashboard and can change the date range accordingly. But it doesn’t display weeks where there were zero, so I can’t really see progression week-by-week at a glance, as seeing the zeros is important to view progress when I’m monitoring it by week. So I created the metric to show the zeros, but now it doesn’t allow me to stack by UTM Campaign even though the attribute (when not a created metric with IFNULL) does allow it? Thank you so much for your help with this - probably just a beginner’s lack of understanding!!
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
Other workaround is not to stack using UTM campaign, but place the metric multiple times into Metric bucket and use local metric attribute filters to filter different UTM campaign for each copy of the original metric in insight. This will let you compare up to 20 selected campaigns in one chart.
🎉 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 :)