Apply a filter to a single data item?

[kaevne]

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.

[david.stachon]

is this off of a relational or dimensional model?

[kaevne]

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.

[kaevne]

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.

[kaevne]

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?

[kaevne]

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.