I have to generate a report of invoices in Looker. Each invoice belongs to a category field. I am tasked with computing statistics of that category, and comparing each individual invoice against that statistic.
Here is an example. Suppose invoice 0 belongs to category A and all the invoices in category A have an average field 0 value of 10. The report should alert when the field 0 of a given invoice deviates from the average of the category by a certain amount. In this example lets say that deviation trigger is 5.
| Invoice | Category | Average Field 0 Value of Category | Invoice Field 0 Value | |
|---|---|---|---|---|
| Invoice 0 | A | 10 | 3 |
In this case the report should send an alert because field 0 value of Invoice 0 is more than 5 off from the average field value of its category.
The statistics are not provided in the database. All I have is the invoice category, and the Field 0 value for each invoice. I cannot manually enter the statistics (average) for each category. There are thousands of categories. Creating a new field in the database for the statistics of the category is also not an option. I also cannot create a new measure field (unless I really have to).
I need to compute the statistic (average) in the report for each category then compare each invoice against that statistics for its provided category. Everything needs to be done in the Looker Dashboard creation interface. I was hoping there was some fancy things I could do with calculations, filters, pivots, or anything else in the Looker Dashboard creation interface.
Thanks for your help. let me know if I can clarify anything.
I tried using calculations and reading Looker docs but could not find anything that suits my needs.
