18 Replies Latest reply: Jul 22, 2016 10:43 AM by Cliff Clayman

# Straight table combining expressions

I have a straight table with one Dimension, Name and two expressions, # of Purchases and YTD Amount.  The result set relies on the expressions working together, meaning, I need to only see the total # of Purchases that are greater than 10 and where the YTD Amount is greater than 10,000.  How can I write the two expressions?

• ###### Re: Straight table combining expressions

Give a try on the below:

Sum({<[# Of Purchases]={"\$(=Count([# Of Purchases])>10"},[YTD Amount] = {'>10,000'}>} [# Of Purchases])

If this is not helping, can you please post a small sample.

• ###### Re: Straight table combining expressions

That is only one expression.  Is the YTD Amount expression just a straight forward Sum?

• ###### Re: Straight table combining expressions

The expression I gave you have the both conditions:

It will sum [# Of Purchases] only if count of [# Of Purchases] are greater than 10 and [YTD Amount] >10,000

Is that is what you are looking for ?

• ###### Re: Straight table combining expressions

No, the table will have 3 columns.  Name, # of Purchases, YTD Amount.

I only want to see the # of Purchases that are greater than 10 and the YTD Amount is greater than 10,000.

So, I believe I will need two expressions.

• ###### Re: Straight table combining expressions

Try as below:

Dimension: Name

Expression1:

Count({<[# Of Purchases]={"\$(=Count([# Of Purchases])>10"},[YTD Amount] = {'>10,000'}>} [# Of Purchases])

Expression2:

Sum(YTD Amount)

Hope this helps...

• ###### Re: Straight table combining expressions

[YTD Amount] is not a field that exists...it is an expression.

• ###### Re: Straight table combining expressions

Can you please share a small sample ....

• ###### Re: Straight table combining expressions

In this example, I only want to see the results for Dave Donaldson and Tom Thompson.

• ###### Re: Straight table combining expressions

What is your expression for YTD Amount here?

May be like this:

Count({<Name = {"=Count([# Of Purchases]) >10 and Expression for YTD Amount >10000"}>} [# Of Purchases])

Replace Expression for YTD Amount with your expression you have used for YTD Amount.

• ###### Re: Straight table combining expressions

That is part of the issue.  I don't know what the expression for YTD Amount should look like...

• ###### Re: Straight table combining expressions

Please take a look at the attached:

• ###### Re: Straight table combining expressions

The # of Charges needs to be a Sum and the expression for YTD Amount only works with the small subset of data.  There needs to be a check for the dollar amount as well as the total # of charges.

• ###### Re: Straight table combining expressions

Can you let us know the expression that you have used in the excel got YTD Amount..

• ###### Re: Straight table combining expressions

The issue is that I do not know what the expressions should be.  I have 3 fields.  Name, Charges, Amount.  I have multiple names with multiple charges and amounts.  I need an expression that will Sum the vales for Charges and Amounts.  I then need to filter the results for charges that are greater than 10 AND where the amounts are greater than \$10,000.  I don't know if it would be easier to create a calculated dimension that does the check for the condition and suppress null values and then just do the Sum of the charges and amounts in an expression.

• ###### Re: Straight table combining expressions

Hi Cliff

First expression goes like this

Sum({<[# Of Purchases]={"\$(=Count([# Of Purchases])>10"}>} [# Of Purchases])

And second one

Sum({<[YTD Amount] = {'>10,000'}>}[YTD Amount])

Thanks

Karthik

• ###### Re: Straight table combining expressions

None of these solutions seem to fit.  Is there a way to use Calculated Dimensions to make this easier?

The issue is that I do not know what the expressions should be.  I have 3 fields.  Name, Charges, Amount.  I have multiple names with multiple charges and amounts.  I need an expression that will Sum the vales for Charges and Amounts.  I then need to filter the results for charges that are greater than 10 AND where the amounts are greater than \$10,000.  I don't know if it would be easier to create a calculated dimension that does the check for the condition and suppress null values and then just do the Sum of the charges and amounts in an expression.