topic Re: How do i create a Sales Index in QlikView

How do i create a Sales Index

Wed, 26 Jun 2013 03:56:31 GMT

Hi! Im interested on creating a sales index based on year 2010. I´m using a pivot table so that rows show months and coloms show years.

Im using the following:

(Sum(Sales )) /sum( {1<YEAR= {2010} >} Sales)

But its not working, it just gives mi "1"'s in 2010 column an "-"'s in the other colums

Please help

Re: How do i create a Sales Index

sujeetsingh — Wed, 26 Jun 2013 04:07:54 GMT

Sendy,

Try this

(Sum(Sales )) /sum( {$<YEAR= {2010} >} Sales)

Re: How do i create a Sales Index

Wed, 26 Jun 2013 04:10:29 GMT

The same. Still not working

Re: How do i create a Sales Index

sujeetsingh — Wed, 26 Jun 2013 04:22:44 GMT

plz give a sample

Re: How do i create a Sales Index

Wed, 26 Jun 2013 04:34:07 GMT

i leave u the images

Re: How do i create a Sales Index

Wed, 26 Jun 2013 04:35:38 GMT

Try using (Sum(Sales )) /sum({1<Year= {2010} >} TOTAL Sales)

Re: How do i create a Sales Index

Wed, 26 Jun 2013 04:39:51 GMT

By doing that it divides the value of each month by the whole value of the 2010, and what i want to do is this:

month(i)/month(i,year2010)

Re: How do i create a Sales Index

sujeetsingh — Wed, 26 Jun 2013 04:48:33 GMT

Sendy please explain that what calculation you want.

Dimension i?

Re: How do i create a Sales Index

Wed, 26 Jun 2013 04:51:28 GMT

(month)/(the same month on 2010)

example

for feb 2012:

feb2012/feb2010

for jan 2013:

jan2013/jan2010

and so...

in a matrix notation where we have months and years

for the position a;b

(month (a), year (b)) / (month (a) ; year(2010) )

Re: How do i create a Sales Index

Wed, 26 Jun 2013 06:11:45 GMT

Take Month as dimension..

SET vMaxYear=MAX(Year) //(2013)

sum({<Year={$(vMaxYear)}>} value)/sum({<Year={$(=Max(Year(Year))-3)}>} value)

Regards-Bika

Re: How do i create a Sales Index

daveamz — Wed, 26 Jun 2013 06:47:30 GMT

Hi,

Try sum(Sales)/Before(sum(Sales),ColumnNo()-1)

See the attached example.

Regards,

David

Re: How do i create a Sales Index

sujeetsingh — Wed, 26 Jun 2013 06:49:42 GMT

See this

Re: How do i create a Sales Index

Sat, 29 Jun 2013 01:33:04 GMT

Can u post the example as an image, i cant open other .qvw as i use the trial version.

Thanks

Re: How do i create a Sales Index

Gysbert_Wassenaar — Sat, 29 Jun 2013 08:58:27 GMT

You can't use set analysis here since you use Year as dimension. You need something like:

if(Year=2010,1,sum(Sales)/before(sum(Sales),Year-2010,1))

Re: How do i create a Sales Index

swuehl — Sat, 29 Jun 2013 09:07:54 GMT

By doing that it divides the value of each month by the whole value of the 2010, and what i want to do is this:
month(i)/month(i,year2010)

Just add your Month field (MES) to the TOTAL field list

Sum(Ventas) / sum({1<ANO= {2010} >} TOTAL<MES> Ventas)