topic How to make a graph showing only growth over period? in QlikView

How to make a graph showing only growth over period?

Thu, 16 Oct 2014 07:11:14 GMT

Let say I have a database:

Year	Department	Product	Sale
2008	US	Socks	1000
2009	US	Socks	2000
2010	US	Socks	1500
2011	US	Socks	1700
2008	UK	Socks	2000
2009	UK	Socks	2500
2010	UK	Socks	3000
2011	UK	Socks	3100

I want to create a graph showing only the growth for each given year. So the graph would use information like that:

Header 1	Header 2	Header 3
2009	US	1000
2010	US	-500
2011	US	200
2009	UK	500
2010	UK	500
2011	UK	100

As I understand it can be done so that while creating a graph I'll prepare each year expression separately.

But I would want more simple solution as I would have more than 10 years to show and would want to use it constantly.

Would it be possible and effective to great the additional field for each row showing the Last Year Sale also.

For example making resident load of database where 'Year+1 as Year' and then outer joining it back to database.

So when creating a graph I could say that 'Change' would be 'Sale - LastYearSale' or 'Sale/LastYearSale-1' when wanting to show it as a %.

As I have a database with 40 millions rows and 30 dimensions is the outer joining a problem? Is the better solution?

Re: How to make a graph showing only growth over period?

Thu, 16 Oct 2014 07:32:55 GMT

Hi,

You can use the below expression to calculate sum(Sales)-Sum(Lastyearsales)

Sum(Sales) - Sum({<Header1={'=$(Header1-1)'}>}Sales)

Replace Header1 with your Year dimension

Re: How to make a graph showing only growth over period?

perumal_41 — Thu, 16 Oct 2014 07:40:48 GMT

Try this

Sum(Sales) -above(Sum(Sales))

Re: How to make a graph showing only growth over period?

Thu, 16 Oct 2014 07:48:58 GMT

Thank you for the quick answer.

sum({<Year={'=$(Year-1)'}>}Sales) shows zeros for every row for some reason

Re: How to make a graph showing only growth over period?

mdmukramali — Thu, 16 Oct 2014 08:30:27 GMT

Dear,

Can you check the attached file.

I hope it will help you.

Thanks,

Mukram

Re: How to make a graph showing only growth over period?

Thu, 16 Oct 2014 10:26:48 GMT

Suggested solutions work until I do not include other dimensions. And the formula is getting more complex.

Wouldn't be better to create a extra "LastYearSale" column to database? What are the negative side of it? It should be faster and its should not waste much space? Based on googeling, no-one is suggesting that for some reason...