How Can I do this set analysys?

[rcandeo]

I need to do a set analysys making an aggregation to compare if the total value of all documents for an especific client has an amount bigger than a variable called Minimum.

The expression should be something like this:

sum({$<sum(aggr(Total,company,Document,Client))>{'Minimum'}>} Total)

but this doesn't bring me any results.

Only works if I do like this (but it doesn't help me):

sum({$<Total>{'Minimum'}>} Total)

[johnw]

I'm not clear what you're trying to accomplish with the aggr(). Ignoring that part, I think you're looking for something with a structure like this:

sum({<Company={"=sum(...)>Minimum"}>} Total)

[rcandeo]

sorry, but I didn't understand what the comparison with Company is doing in your expression.

I need to aggregate all the values for an especific company, client and document and compare the total amount of this aggregation to compare if it is bigger than a variable that I called Minimum. If these total is bigger than the Miminum I want to show in the table, otherwelse I want to discard this line.

Did you get what I need please?

[d_pranskus]

HI

Just a clue. Could it help to create a artificial dimension called CompanyClientDocument for every row in a fact table and then use it in the set analysis as John suggested.

For example

Company & '|' Client & '|' & Document

Cheers

Darius

[rcandeo]

I've just created the dimension and feeling like a stupid cause I don't know how to use these in the set analysys.

[johnw]

If I understand what you are asking for and what Darius is suggesting, you would then build your chart like this:

Dimensions = Company, Client, Document
Expression = sum({<CompanyClientDocument={"=sum(Total)>Minimum"}>} Total)

The set specifies a search expression that looks for CompanyClientDocument's for which the sum(Total) is greater than the specified Minimum. But the more I think about it, the more I think you don't want set analysis. Just do this:

Dimensions = Company, Client, Document
Expression = if(sum(Total)>Minimum,sum(Total))

I expect both approaches to perform similarly, as both have to do basically the same amount of work to get your answer. But it's a whole lot more clear what's going on if you do it with an if() and no extra fields.

[rcandeo]

John, the second expression worked, but gave to me the same result that I had before, where I see the lines (with a null value) which amount total is less that the minimum and I don't want to see those lines.

Using the second expression brigs to me a null value and I didn't find out what is wrong:

sum({<Agregation={"=sum(Total)>Minimum"}>} Total)

where Agregation is defined in my script as Company&'|'&Client&'|'&Document

[johnw]

Do you have other expressions in your chart? If they have values, the null for the expression we're discussing wouldn't be suppressed. But set analysis wouldn't help either. You'd need to add an if(sum(Total)>Minimum,...) for every expression. There would also be ways to fix it in the dimensions, but I'd stick with expressions.