New Column calculation based on a defined date

[nadkalyan]

Hi,

We have a requirement where in we need to create a calculation based on amount for a particular date. Here is the table

Date	Amount	As of Jan1 DIfference
01/01/2012	$1000
01/08/2012	$900	-$100
01/15/2012	$1500	$500
01/29/2012	$1800	$800
02/05/2012	$800	-$200
02/12/2012	$1250	$250
02/19/2012	$1000	$0

As you can see the column I need is 'As of Jan1 Difference' -  It is basically the difference of each row with Jan1, 2012 Amount.

Can someone post any ideas on how to acheive this using expression calculation.

Thanks

Kalyan

[#2]

You can put the value (in this case $1000) in a variable, and then substact it from the daily values.

If you change the date reference, you can put it also in a variable, then you can play changing your reference date.

Hope this help.

[nadkalyan]

Hi dewaegep,

Your solution perfectly works if we have a unchanged Amount for Jan1 where i can put the contant amount as a variable and use this variable for calucluting the difference for each row. But if the Jan 1 Amount changes periodically, then is there a way to include logic to pick up amount for Jan 1 no matter what ever the changed amount is?

Thanks

Kalyan

[MayilVahanan]

Hi,

     Try this,

     In a variable for example, vTest = Sum({<Date = {'01/01/2012'}>}Amount)  assign it.

     And used in expression like =if(Date <> '01/01/2012', Amount - vTest, '')  or simply Amount - vTest as per your requirement..

     And Untick Suppress Zero Values in Presentation tab..

Hope it helps

[nadkalyan]

Hi Mayil,

I get all 0's for the Variable calculation you provided.

Thanks

Kalyan

[MayilVahanan · Accepted solution]

Hi,

     Check the attached file.

     Hope it helps

[#7]

Kalyan,

the example of Mayil is what I was meaning. Depending on your script, you can load the amount of January in the variable during the data loading process. Just create the variable in the loading script.

Tell us if your satisfied with the Mayil solution. And put his response as the correct answer.

[nadkalyan]

Thanks Mayil and dewaegap,

The example provided by Mayil perfectly worked in our case.

Thanks both

Kalyan