topic Re: Expression in pivot table in QlikView

Expression in pivot table

Fri, 03 Aug 2012 12:15:59 GMT

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

Fri, 03 Aug 2012 12:25:10 GMT

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

Re: Expression in pivot table

Mon, 06 Aug 2012 07:34:01 GMT

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

Mon, 06 Aug 2012 07:57:30 GMT

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

Wed, 08 Aug 2012 08:44:15 GMT

Hi Rahul,

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

Re: Expression in pivot table

Wed, 08 Aug 2012 09:04:14 GMT

Hi,

Could you please post the QVW as an exampl

Regards

Rahul

Re: Expression in pivot table

Wed, 08 Aug 2012 10:04:49 GMT

Thanks for your help Rahul.

I am able to achieve the desire result with expression

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