I have a crosstab with a number of data items. I would like to apply a filter to a single data item expression.
This is the expression I have:
average(filter(countable_time, complete_record = 1))
For each cell in the row, I want my data item to display the average countable_time for that column for all records that have a complete_record = 1. However, this simply gives me a bunch of 1's. Is my syntax incorrect? What am I missing?
PS. I do not want a conventional detail filter because it applies to the whole crosstab's query. If I just had an expression: average(countable_time) and a detail filter (complete_record = 1), then it gives me the correct values, but also only the columns where complete_record = 1 is satisfied.
is this off of a relational or dimensional model?
I believe it's relational, but my DB knowledge is not that high. To be exact, I have two tables, the query uses a join between the two tables with a two-sided link of = 1..n and 1..1.
If anyone's interested this is how I resolved it:
I created two queries and with different detail filters and joined them. It was still giving me the same data but then I realized that my joins were incorrect. I made one of them an outer join so it displays one query set even when the other didn't have any corresponding data. Bam problem fixed.
We're now running into the issue where we have more than 2 data items that need different filters. Anyone have a way to apply filters to single data items?
Ok I'm going to reply to my own post for the 5th time. We finally resolved this issue for our relational database. Here's our solution hopefully this helps someone else:
For each query calculation that required different filters, we created a new query. Each query had its own filters but they also had 1 common column that we could join with.
We had 5 queries, total, and ended up using 4 joins to put these together into one final query, almost like a tree heirarchy. A majority of the joins were 0..n 0..n because the queries didn't have anything to do with each other. It's very hacky I know.
In the end, our crosstab used the final, largest, joined query that simply referenced each of the bottom-level queries with its own filters. This finally gave us the data we wanted.