Hi folks, I'm trying to create a particular metric...
# gd-beginners
d
Hi folks, I'm trying to create a particular metric that can behave well with filters, and I'm not sure how, maybe someone could help. The MRE: I have a label variable X. I need to know the count of unique X per day. Easily enough, the MAQL is Metric 1:
``SELECT COUNT( X )``
...and then I can view it in analysis per day where needed, works great. So, I have another variable Y. My daily Count of X is meant to be a denominator of my daily count of Y. i.e., each day, I need to know how many Ys happen per X. Metric 2:
``SELECT COUNT( Y ) / SELECT COUNT( X )``
... and again, I can view it easily enough per day where needed. Lastly, all of my insights that use Metric 2 are subject to filters on variable Z. The filter on Z values should only apply to Y, and not X. That's the part I cannot figure out how to do. Does anybody know what to do in this case? Thank you!
solved, but for any other beginners: just create a metric 1 as: COUNT( X) WITHOUT PARENT FILTER and reference that metric in place of COUNT( X ) in creating Metric 2
j
Hi Daniel, Glad to hear you were able to resolve this. The documentation for this can be found here: https://www.gooddata.com/docs/cloud/create-metrics/maql/filters/overriding-parent/without-parent-filter/
1
d
Actually, there's still a hiccup. I've now got: Metric 2:
``SELECT count({label/Y}) /{metric/Metric 1}``
Where Metric 1:
``select count({label/X) WITHOUT PARENT FILTER``
But it's still not reacting how I'd like. The problem isn't the actual filters, but inherent filtering that's apparently happening inside of widgets independently. So I have stacked bar graphs that stack Metric 2 according to another variable V. Within each stack layer, the the Metric 1 within Metric 2 is being filtered on the stack layer's V value. This throws off my visualization, as the per-V-value stacks are summing to a height far greater than what Metric 2 would be were I not breaking up the visual into stacks
j
Could you please provide screenshots of what you are seeing on your end with the stack bar graphs?
d
Thanks Joseph, sure. These two insights are identical, except that the latter has a stacking variable of car brand, and is behaving unexpectedly. Connecting to my abstract description above: • The vertical axis on both charts is "Metric 2", shown as a percent. • The parent "ID industry" filter, set to the automobile industry (versus some other industry) is what I'd called Z, and it's the parent filter that I've gotten my metric to behave properly in response to. • Troublingly, the stacks in the rightmost widget are each either 0% or 100% for each day, which makes no sense. My intent is that the topmost line of the stacks on rightmost widget should be identical to the top line on leftmost widget
j
You perhaps need to use some BY rule in MAQL of the denominator. Try
``BY ALL {lavel/V}``
or
``BY {label/date.day}, ALL OTHER``
. Tip for troubleshooting: display the chart as table with both components of fraction: nominator and denominator if you want to see the root cause of unexpected results.
🙌 2