6 Replies Latest reply: Aug 8, 2012 6:04 AM by ramkumar24

Expression in pivot table

In my report I am using the Pivot table to show the data, the structure is similat to below one

Dec-11                                                                Jan-12

-----------------------------------------------------    ---------------------------------------------------------

Amount               Diff Amount,(m-1)             Amount                   Diff Amount,(m-1)

----------------------------------------------------    ---------------------------------------------------------

3500                         0                                     5000                         1500

I am using expression to calculate the value of "Diff Amount,(m-1)" field, but somehow its not working as expected or may be i am doing something wrong.

Please do help to get the expected result.

• Re: Expression in pivot table

Hi,

That most likely is the case because of the the year change.

Try Like:

sum({\$<Year = {\$(=only(Year) - \$(=if(only(Month) = 1, 1, 0)))}, MonthYear = {\$(=month(addmonths(MonthYear, -1)))}>}DiffAmount)

Where MonthYear is your month field and DiffAmount is the field you are trying to manipulate.

Regards

RL

• Re: Expression in pivot table

Hi Lakhina007,

Thanks for your reply, but seems not able to put my problem clearly. So here providing more detail for clarity,

"DiffAmount" field I am not getting from the source database, only amount and month field is available.

It is getting calculated at QlikView end as "[current month amount] - [previous month amount]",

like    Jan-12 DiffAmount = 5000 - 3500 = 1500

Regards,

ramkumar

• Re: Expression in pivot table

Try Like:

Sum(Amount) - (sum({\$<Year = {\$(=only(Year) - \$(=if(only(Month) = 1, 1, 0)))}, MonthYear = {\$(=month(addmonths(MonthYear, -1)))}>}Amount) )

This is based on the assumption the you want the user to select the current month.

However if you want to use the current month you could try:

Sum(if( Date>= Montstart(today()),Amount)) - Sum(if(Date>=  Monthstart(Montstart(today())-1) and Date < Montstart(today()),Amount))

Where Date is your Date Field.

Regards

Rahul

• Re: Expression in pivot table

Hi Rahul,

Thanks for the expression. But its not working, not getting any result

• Re: Expression in pivot table

Hi,

Could you please post the QVW as an exampl

Regards

Rahul

• Re: Expression in pivot table

Thanks for your help Rahul.

I am able to achieve the desire result with expression

sum( Amount ) - before(sum( Amount ))