topic Re: Pivot Table Calculation in QlikView

Pivot Table Calculation

rido1421 — Wed, 20 Jul 2016 10:34:31 GMT

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.

Your assistance is appreciated.

PrashantSangle — Wed, 20 Jul 2016 10:38:36 GMT

Hi,

try this,

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

Regards

Gysbert_Wassenaar — Wed, 20 Jul 2016 10:44:10 GMT

Perhaps like this:

If(SecondaryDimensionality()<> 0,

Sum(Amount),

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

)

trdandamudi — Wed, 20 Jul 2016 10:46:14 GMT

Try as below:

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

rido1421 — Wed, 20 Jul 2016 10:57:45 GMT

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?

Gysbert_Wassenaar — Wed, 20 Jul 2016 11:01:49 GMT

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

rido1421 — Wed, 20 Jul 2016 11:40:54 GMT

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) "

Gysbert_Wassenaar — Wed, 20 Jul 2016 11:49:32 GMT

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

sunny_talwar — Wed, 20 Jul 2016 12:08:11 GMT

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?