topic Re: Re: Re: Re: Creating a Table similar to Attached excel file in QlikView

Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 10:26:01 GMT

Hello All,

I want to create a table as the table in the excel file attached.

Year 1.....Year 2 represents the assets years they are in service. I want to start with the first Year when are placed in service and till its useful life completes.

Year1 Residual Value, Net Book Value and useful life are given to me, rest all needs to be calculated.

There are formulates in excel for reference.

I need to create replica in qlikview.

Re: Creating a Table similar to Attached excel file

rubenmarin — Tue, 28 Oct 2014 10:36:37 GMT

Hi Alkesh, seeing the Excel it seems you also need the % of depreciation expense per year, I didn't see any logical evolution you can extract from the initial data given. Or how you obtain that value?

Re: Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 10:50:20 GMT

Hey Ruben, That (100/1000) are the two fixed value that user would provide. or Take it as (var1/var2) where var 1 and var 2 will be provided by the user.

Re: Creating a Table similar to Attached excel file

Tue, 28 Oct 2014 11:05:59 GMT

Hi Alkesh,

1. Load the excel with the values provided to you.

2. Use a Straight table (Under Charts) , select Year as ur dimension.

3.Under expression add the fields you have and also add the calculations under the definition of the expressions and label them accordingly.

The calculations are very simple... you can directly write the mathematical expressions with the field names u have.

I hope it helps.

Re: Re: Creating a Table similar to Attached excel file

rubenmarin — Tue, 28 Oct 2014 11:08:54 GMT

Hi, Check attachment, in script i made the percentage provided by year.

Re: Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 11:14:04 GMT

Hey Snehaleela,

If you look at the calculations carefully, its not as simple as creating it in a straight table.

The Net Book value of Year 2 is the Year end book value of Year 1.

This needs to be scripted in the back-end probably using previous or before function.

It would be highly appreciable if you can help.

Re: Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 11:18:48 GMT

Hey Ruben,

Well this is not exactly what I needed but I guess I can tweak it to get the result.

Thanx a ton,

Alkesh

Re: Re: Re: Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 11:36:32 GMT

Hey Rubin, a little more help

What would if I have the data in the attached format.

I need to create the table for every Asset category and I need to create the Year1 Year 2 etc from the capitalization and Useful life.

For: eg: Capitalization Date = 1/1/2000 and useful life is 10 than year would be 10 years from 1 to 10 or if the use ful life is 15 than there would be 15 years from 1 to 15.

Please Help.

Re: Re: Re: Creating a Table similar to Attached excel file

rubenmarin — Tue, 28 Oct 2014 11:47:27 GMT

Hi Alkesh, but there you don't know wich depreciation percentage applies to each year per asset, can you expect it will be equal aprt for every year? ie, for 10 year of useful life, it corresponds a 10% to each year?

Re: Re: Re: Creating a Table similar to Attached excel file

alkesh_sharma — Tue, 28 Oct 2014 12:01:46 GMT

I guess we can do that, we can do 100% / no. of years

Re: Re: Re: Re: Creating a Table similar to Attached excel file

rubenmarin — Tue, 28 Oct 2014 12:20:44 GMT

Check attachment, if you select an asset the table will show the values

Re: Re: Re: Re: Creating a Table similar to Attached excel file

rubenmarin — Tue, 28 Oct 2014 12:30:40 GMT

I already see, I think it doesn't gives the expected result for the 9,5 lifetime asset, or is it ok?

Re: Re: Re: Re: Re: Creating a Table similar to Attached excel file

alkesh_sharma — Wed, 29 Oct 2014 06:11:58 GMT

Hey Ruben,

It's Not working for any asset.. or guess I am doing it wrong.

Here is the attached snapshot.

I do selected the asset category but there is no value displayed in the table above..

Re: Re: Re: Re: Re: Creating a Table similar to Attached excel file

alkesh_sharma — Wed, 29 Oct 2014 06:54:05 GMT

I figured it out, Its working... Thanx for the help.

But I do need to create depreciation tables for different depreciation Methods..

I might require your help soon for them as well.....

Cheers and Thanx a ton!