topic Re: Calculate value based on no data in QlikView

Calculate value based on no data

Thu, 12 Dec 2013 17:31:40 GMT

Hello. I hope somebody could tell me how to do the following (and if it's possible using QlikView 11)

I need to create report for stock analysis and one of the calculated properties burns my mind.

There are entities named Portfolio. These portfolios contain a sets of positions (wich are stocks).

Each position starts some day from 1000$ and ends in another day with some value

For example:

Bob's portfolio

Date	MS	APPL
1
2		1000
3		800
4	1000	1500
5	1300	1400
6	1400
7	1200
8
9
10

Now I need to calculate a specific value for this whole portfolio for days 3-9. and here is how It should be calculated

(SUM(ALL_POSITIONS_END_DATE) / SUM(ALL_POSITIONS_START_DATE))

And here is the trick. My start date is 3 so MS should be 1000 and APPL 800 => SUM() = 1800

My end date is 9 so MS should be 1200 and APPL 1400 => SUM() = 2600

2600 / 1800 = 1,4444

But there are no data for MS days 3, 8, 9 and APPL days 6-9. I need to emulate it as I have the following dataset

Bob's portfolio

Date	MS	APPL
1
2
3	1000	800
4	1000	1500
5	1300	1400
6	1400	1400
7	1200	1400
8	1200	1400
9	1200	1400
10

Re: Calculate value based on no data

maxgro — Thu, 12 Dec 2013 17:45:28 GMT

If you can populate the fields in the load script you can follow this

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field

Re: Calculate value based on no data

Anonymous — Thu, 12 Dec 2013 17:57:05 GMT

lAnubisl

You could try creating a Master Calendar containing all dates from a suitable Start Date to a suitable End Date, and join that to your Fact table.

Then you'll have all the dates you require to report on, albeit some dates will have no rows in the Fact table but that can be accommodated.

Best Regards, Bill

Re: Calculate value based on no data

greg-anderson — Thu, 12 Dec 2013 18:22:05 GMT

Have you tried the FIRST() and LAST() functions on each column? They should pull the first and last value, respectively, for each field/column, within the selected date range.

Re: Calculate value based on no data

Sat, 14 Dec 2013 08:10:34 GMT

Hello Greg

First() and Last() cannot be used inside aggregation functions such as Sum()

Re: Calculate value based on no data

Sat, 14 Dec 2013 09:04:53 GMT

Hello Massimo.

That is a fantastic blog post but I'm afraid it will not solve my problem.

Here is a link to example QW file.

And here is a screenshot from it.

I need to make it work for each PortfolioId, Stock records separately.

2 1 MS 5

2 1 APPL 8

3 1 MS 4

3 1 APPL 8

4 1 MS 7

4 1 APPL 8

5 1 MS 6

5 1 APPL 6

......

Re: Re: Calculate value based on no data

maxgro — Mon, 16 Dec 2013 23:18:11 GMT

Aleksander, check the image (I added some stocks and portfolios) and if answers to your question see the attachments .qvw