topic Re: Calculate sales for previous year in QlikView

Calculate sales for previous year

Wed, 23 Jul 2014 06:55:12 GMT

Hi!

I wan't to use year as dimension and calculate the sales for the previous year. How do I do this if I wan't to view 4 years at the same time? Like this:

Year	Sales
2014	Sales for 2013
2013	Sales for 2012
2012	Sales for 2011
2011	Sales for 2010

How do I do this in the easiest way?

Thanks in advance!

Re: Calculate sales for previous year

alexandros17 — Wed, 23 Jul 2014 07:05:08 GMT

If you have a date for sales for example sale_date then when you load your sales data add a field year(sale_date) as year, in this way simply doing a sum of sales values in the expression (using year as dimension) you will have whqat you need

Let me know

Re: Calculate sales for previous year

its_anandrjs — Wed, 23 Jul 2014 07:08:40 GMT

Its seems like rolling 4 years from current year. Work on the rolling years.

Re: Calculate sales for previous year

maleksafa — Wed, 23 Jul 2014 07:14:36 GMT

you have many ways to achieve that depending on your data model, you can prepare the previous sales in the script using the peek function on each year to peek the sales of the previous year.

you can do that from the chart using set analysis, where you will define a variable called vPreviousYear which is the selected year - 1 and use it when calculating the sales of this year, or if the years are sorted you can use the below function to get the sales of the below row for example the below sales for year 2014 will return the sales of 2013.

Re: Calculate sales for previous year

PradeepReddy — Wed, 23 Jul 2014 07:17:51 GMT

If you want to showcase the data in table format , see the attachment.

Re: Calculate sales for previous year

its_anandrjs — Wed, 23 Jul 2014 07:41:18 GMT

Hi Kim,

Easiest way of solve this problem would be i explain with an example see the inline table here i take 7 years sales data

and in the front end i take the straight table

LOAD * INLINE [

Year, Sales

2014, 43432

2013, 43244

2012, 34556

2011, 54545

2010, 45365

2009, 45356

2008, 45653

];

1.Straight table with dimension and expression

Dimension:- Year+1

Expression:- sum({<Year= { '>=$(=max(Year)-4) <=$(=max(Year)-1) '}>} Sales)

Note:- As you describe you need the data as and this chart is dynamic when you select 2012 year this will gives last 4 years data.

Year	Sales
2014	Sales for 2013
2013	Sales for 2012
2012	Sales for 2011
2011	Sales for 2010

And then in the straight table you get data as mention above

Regards

Re: Calculate sales for previous year

Wed, 23 Jul 2014 07:56:48 GMT

Thanks for your reply.

I tried using the above-funktion but it returns null for the first year, which is correct but I wan't to hide this row, is that possible?

Re: Calculate sales for previous year

alexandros17 — Wed, 23 Jul 2014 08:01:05 GMT

Check (in the presentation tab) the box "suppress zero values"

Re: Calculate sales for previous year

Wed, 23 Jul 2014 08:35:04 GMT

Tried that, didn't help I'm afraid.

Re: Calculate sales for previous year

alexandros17 — Wed, 23 Jul 2014 08:39:34 GMT

Could you attach your document?