Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable

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

6 Replies
Not applicable

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

Not applicable

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

Not applicable

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

Not applicable

Re: Expression in pivot table

Hi Rahul,

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

Not applicable

Re: Expression in pivot table

Hi,

Could you please post the QVW as an exampl

Regards


Rahul

Not applicable

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

Community Browser