Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

Thanks for your help Rahul.

I am able to achieve the desire result with expression

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

View solution in original post

6 Replies
Not applicable
Author

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
Author

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
Author

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
Author

Hi Rahul,

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

Not applicable
Author

Hi,

Could you please post the QVW as an exampl

Regards


Rahul

Not applicable
Author

Thanks for your help Rahul.

I am able to achieve the desire result with expression

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