Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks for your help Rahul.
I am able to achieve the desire result with expression
sum( Amount ) - before(sum( Amount ))
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
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
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
Hi Rahul,
Thanks for the expression. But its not working, not getting any result
Hi,
Could you please post the QVW as an exampl
Regards
Rahul
Thanks for your help Rahul.
I am able to achieve the desire result with expression
sum( Amount ) - before(sum( Amount ))