Error on insights query with filter count

###Insights Question Template
I’m trying to get a adjusted error rate across apps and time. I was planning to use FILTER to achieve this but I just get an error:

Something went wrong while executing your query.

Query:

SELECT
  FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App1'),
  FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App2'),
  FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App3')
 FROM Transaction
  FACET DATEOF(timestamp) SINCE 7 days AGO

If I remove the rate calculation, the query executes:

SELECT
  FILTER(count(errorMessage), WHERE appName = 'App1'),
  FILTER(count(errorMessage), WHERE appName = 'App2'),
  FILTER(count(errorMessage), WHERE appName = 'App3')
 FROM Transaction
  FACET DATEOF(timestamp) SINCE 7 days AGO

But this doesn’t give the result I need.

Ideas?

Hi @jose.sa - This should give you the results that you required.

SELECT
  count(errorMessage)/count(*) * 1000 AS 'Display value'
FROM Transaction
WHERE appName IN('App1', 'App2', 'App3')
FACET appName, dateOf(timestamp) SINCE 7 days ago

It gives some data but not in the format I was looking for:

              App1    App2 
Day 1       0.0         0.2
Day 2       1.0         0.1

What format did you require?

This is pretty close to what I want to get:

SELECT
FILTER(percentage(count(*), WHERE errorMessage IS NOT NULL), WHERE appName LIKE 'App1') AS 'App1',
FILTER(percentage(count(*), WHERE errorMessage IS NOT NULL), WHERE appName LIKE 'App2') AS 'App2',
FILTER(percentage(count(*), WHERE errorMessage IS NOT NULL), WHERE appName LIKE 'App3') AS 'App3'
FROM Transaction
FACET DATEOF(timestamp)  SINCE 7 DAYS AGO

But the percentage rounds the number to high and I get with a bunch of 0s.

Something to validate is running some metrics on the TransactionError table for the same apps to see if you are reporting all errors with the above query.

Getting stuff like:

November 26, 2018 0.07
November 25, 2018 0.07
November 24, 2018 0.03
November 23, 2018 0.05
November 22, 2018 0.09
November 21, 2018 0.08
November 20, 2018 0.08
November 19, 2018 0.09

vs

November 26, 2018 0.72
November 25, 2018 0.7
November 24, 2018 0.34
November 23, 2018 0.53
November 22, 2018 0.91
November 21, 2018 0.85
November 20, 2018 0.81
November 19, 2018 0.91

I will see if i this would be enough for us but I would still like to understand what is wrong with the query.

What is the query for the above problem you are trying to sort out?

Again, I would like for this query to work:

Specially since I would like to have a more complex filter than just a simple appName.

If your issue is the decimal location I would think changing your math from 1000 to 100 would work.

Hi @jose.sa, just wondering if you were able to solve this with @6MM and @stefan_garnham’s help?

The problem is the other way around:

 FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App1'),

Doesn’t work! As an work around i’m using

FILTER(percentage(count(*), WHERE errorMessage IS NOT NULL), WHERE appName LIKE 'App1') AS 'App1',

But the percentage is based on 100 and doing

FILTER(percentage(count(*), WHERE errorMessage IS NOT NULL) * 10, WHERE appName LIKE 'App1') AS 'App1',

Doesn;t work either.

I think you might be making it too complex.

Maybe something like this?

Select FILTER(count(*), Where errorMessage IS NOT NULL) / count(*) * 100 FROM Transaction WHERE appName = 'App1' FACET DATEOF(timestamp) SINCE 7 days AGO

Hi @jose.sa! Eager to see if @6mm was able to help you get closer to what you need! Just reach back out if you need anything else, and of course, feel free to share your solution here with the rest of the community!

Hi,

The problem is that the simplified examples that are giving don’t address the original requirement:
I don’t want to filter 1 app, I require multiples ones. Hence the initial case has multiple filters.

SELECT
FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App1'),
FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App2'),
FILTER(count(errorMessage)/count(*) * 1000, WHERE appName = 'App3')
FROM Transaction
FACET DATEOF(timestamp)
SINCE 7 days AGO

The suggestions try to workaround this by moving the appName filter to outside of the filter but that doesn’t work if the value needs to be per appName.

Hi @jose.sa - If you can provide us an example of the output you are looking for, we can then assist with the query to provide it.

As mentioned before, I want the evolution of the error rate per day per app for multiple apps

            App1     App2    App3
Day 1       0.0      0.2     0.4
Day 2       1.0      0.1     1.0
Day 3       1.0      0.1     0.9
...

The query I provided will give you that table structure, is it the format of the numbers that you would like changed?

Hi @stefan_garnham

the query you suggested:

gives:

App1   December 10, 2018   0.17
App1   December 7, 2018    0.12
App2   December 10, 2018   0.09
App1   December 4, 2018    0.07
App3   December 10, 2018   0.06
App2   December 7, 2018    0.06
App2   December 5, 2018    0.05
App3   December 9, 2018    0.05
App1   December 8, 2018    0.05
App3   December 4, 2018    0.05

Which is not exactly what i’m looking for…

Hey, @jose.sa I think the problem you’re running into here is trying to include multiple functions in a filter. If you break out each function into its own filter, the query in your original post works fine.

Try this:

SELECT
  filter(count(errorMessage), WHERE appName = 'App1') / filter(count(*), WHERE appName = 'App1') * 1000 AS 'App1',
  filter(count(errorMessage), WHERE appName = 'App2') / filter(count(*), WHERE appName = 'App2') * 1000 AS 'App2',
  filter(count(errorMessage), WHERE appName = 'App3') / filter(count(*), WHERE appName = 'App3') * 1000 AS 'App3'
 FROM Transaction
  FACET dateOf(timestamp) SINCE 7 days AGO