Crosstab Filter out records where each day of date field is less than X

[georgezmCognos]

Hello!

I have a simple crosstab where I have the last 7 business days in the columns and a revenue measure. I want to filter out any record where the measure does not exceed $X in any of the days. So if one day has less than X, I need the entire row to be filtered out.

thank you kindly for your suggestions!

[MFGF]

Hello!

I have a simple crosstab where I have the last 7 business days in the columns and a revenue measure. I want to filter out any record where the measure does not exceed $X in any of the days. So if one day has less than X, I need the entire row to be filtered out.

thank you kindly for your suggestions!

Hi,

Are you using a relational package or a dimensional package? The answer will be different for each, so it's important to know.

For a relational package, you'd use a detail filter with an expression such as total([revenue] for [your business day item]) <= X

For a dimensional package, you'd replace the set of members in the columns with a dimensional expression in a query calculation

filter([your set of business day members], [revenue] <= X)

Cheers!

MF.

[dougp]

I hadn't considered the aspect of the dimensional model since I don't use them.

filter out any record where the measure does not exceed $X

So, if X = 100,000, and you have row labels for last name and first name, your filter may look like:

Code Select Expand

minimum(total([Revenue] for [Last name],[First name], [Date]) for [Last name],[First name]) > 100000

Code Select Expand

total([Revenue] for [Last name],[First name], [Date])
seems redundant if you do it alone.  In a simple list including last name, first name, date, and revenue, it's the same as using just [Revenue] with the Total aggregation function, which is probably the default.  But it seems I needed to do that inside the minimum() function to get everything in the right context, using the total aggregation before performing the filter.  I had to do this because I found that setting the filter property "Application" to "After auto aggregation" didn't work, probably because of using an aggregation function in the filter.

[georgezmCognos]

Hello MFGF!

Thank you for your response, you have saved me in the past with one of your solutions, so thank you!

We are using a relational package. For some reason though I'm still not receiving the correct results. There are still records coming through that are lower than 50,000. Does it make a difference if I also have dimensions on the rows? I have 8 different fields that make up the rows and the Date dimension for the columns.

[MFGF]

Hello MFGF!

Thank you for your response, you have saved me in the past with one of your solutions, so thank you!

We are using a relational package. For some reason though I'm still not receiving the correct results. There are still records coming through that are lower than 50,000. Does it make a difference if I also have dimensions on the rows? I have 8 different fields that make up the rows and the Date dimension for the columns.

Hi,

Sorry, I misunderstood - I thought you were looking for rows where the measure was less than or equal to your value. It sounds like you need greater than the value?

You'll need to include the row edge items in the clause also - assuming you are looking at the values in the cells for this requirement.

Doug posted a solution above, which looks good to me (as you'd expect from Doug) :)

Does this work for you?

Cheers!

MF.