Hello everyone,
I'm working with a database that stores documents representing insurance policies or contracts. I've created two segments:
- A segment that identifies documents representing cancellations.
- A segment that identifies documents representing sales.
I have a metric, which is the Cancellation Index, calculated by dividing the number of cancellations by the number of sales, resulting in a percentage of my base that was canceled.
To achieve this, I wanted to create two separate metrics:
- One to count cancellations.
- One to count sales.
However, I can't use the segment as a direct filter in the WHERE clause because one segment excludes the other. For example, a cancellation is never a sale, and if I try something like WHERE cancellation = True AND sale = True, I get 0 rows.
To solve this, I started using the same filters from my segments within a CountIf statement, so I wouldn't filter the records, but instead count only those that meet my conditions. The issue is that I want to avoid duplicating the same filters inside the CountIf that I use in the segments and specify that it should only count rows that belong to the "cancellation" segment in one metric and the "sales" segment in another.
As is:
To be (Like this):
However, when I try to use the segment within the CountIf, I get an error stating that the field is unknown. My code looks something like this: CountIf([cancellation] = True), but this returns an "Unknown Field" error.
Has anyone encountered this issue before, or is there a recommended way to reference segments within metrics in Metabase?
I'm using 0.50.6 version.
Thanks in advance for your help!