topic Re: New Column calculation based on a defined date in QlikView

New Column calculation based on a defined date

nadkalyan — Mon, 09 Jul 2012 17:18:19 GMT

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

Re: New Column calculation based on a defined date

Mon, 09 Jul 2012 17:34:50 GMT

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.

Re: New Column calculation based on a defined date

nadkalyan — Mon, 09 Jul 2012 17:39:55 GMT

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

Re: New Column calculation based on a defined date

MayilVahanan — Mon, 09 Jul 2012 18:08:24 GMT

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

Re: New Column calculation based on a defined date

nadkalyan — Mon, 09 Jul 2012 18:24:41 GMT

Hi Mayil,

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

Thanks

Kalyan

Re: New Column calculation based on a defined date

MayilVahanan — Mon, 09 Jul 2012 18:30:56 GMT

Hi,

Check the attached file.

Hope it helps

Re: New Column calculation based on a defined date

Mon, 09 Jul 2012 22:06:38 GMT

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.

Re: New Column calculation based on a defined date

nadkalyan — Tue, 10 Jul 2012 01:53:39 GMT

Thanks Mayil and dewaegap,

The example provided by Mayil perfectly worked in our case.

Thanks both

Kalyan