8 Replies Latest reply: Jul 20, 2016 8:08 AM by Sunny Talwar

# Pivot Table Calculation

Hi There

I have a pivot table, I am counting the Dates in the last column, I would like to count only the dates where there are values greater than 0

so for Currency1 AUD there should only be a count of 3.

• ###### Re: Pivot Table Calculation

Hi,

try this,

If(SecondaryDimensionality()<> 0,Count(if(Amount>0,Date)))

Regards

• ###### Re: Pivot Table Calculation

Perhaps like this:

If(SecondaryDimensionality()<> 0,

Sum(Amount),

Count({<Amount={'>0'}>}Date)

)

• ###### Re: Pivot Table Calculation

My real expression is something like this  Sum(Aggr(Sum({<Category={'CUS'}>}Distinct Amount),DEALS))

Would I have to create a variable to do it the way you suggest?

• ###### Re: Pivot Table Calculation

No, but if you want to count deals then summing amounts won't give you the answer.

• ###### Re: Pivot Table Calculation

Hi Gysbert

Instead of Sum(Amount) my expression is Sum(Aggr(Sum({<Category={'CUS'}>}Distinct Amount),DEALS))

How would I do this Portion if my expression is as mentioned?

" Count({<Amount={'>0'}>}Date) "

• ###### Re: Pivot Table Calculation

Sorry, I don't understand what you're trying to do.

• ###### Re: Pivot Table Calculation

Would you be able to provide an update sample where you have DEAL in the database to see what exactly are you trying to do?

• ###### Re: Pivot Table Calculation

Try as below:

If(SecondaryDimensionality()<> 0,Sum(Amount),Count({<Amount= {'<> 0'}>}  Date))