topic Re: SALES AND PRE SALES in QlikView

SALES AND PRE SALES

Thu, 20 Mar 2014 12:20:35 GMT

Hi...!!!

Please suggest how to derive

PRE SALES

DIFF

PER(%)

as per the below mentioned details and my data only contains 2 fields i.e YEAR and SALE

YEAR	SALE	PRE SALES	DIFF	PER(%)
2001	2000
2002	2500	2000	500	25
2003	4300	2500	1800	72
2004	2000	4300	-2300	-53.48837209
2005	1000	2000	-1000	-50
2006	5000	1000	4000	400
2007	4678	5000	-322	-6.44
2008	8623	4678	3945	84.33091065
2009	7290	8623	-1333	-15.45865708
2010	4726	7290	-2564	-35.17146776
2011	8564	4726	3838	81.21032586

PRE SALES	PERVIOUS YEAR SALES
DIFF	DIFFERENCE OF CURRENT SALES AND PRE SALES

Thanking You

Vinayagam

Re: SALES AND PRE SALES

its_anandrjs — Thu, 20 Mar 2014 12:25:58 GMT

Hi,

Use Peek() and previous function for this

Thanks & Regards

Re: SALES AND PRE SALES

maxgro — Thu, 20 Mar 2014 12:28:15 GMT

RESULT

SCRIPT

Source:

load * inline [

YEAR, SALE

2001, 2000

2002, 2500

2003, 4300

2004, 2000

2005, 1000

2006, 5000

2007, 4678

2008 ,8623

2009, 7290

2010, 4726

2011, 8564

];

Table:

NoConcatenate

load

YEAR, SALE,

peek(SALE) as PREVSALE,

SALE - peek(SALE) as DIFF,

(SALE - peek(SALE)) / peek(SALE) as DIFF%

Resident

Source

order by YEAR;

DROP Table Source;

Re: SALES AND PRE SALES

Thu, 20 Mar 2014 12:29:15 GMT

Hi,

Dimension: Year

Expression for Sales: sum(SALE)

Expression for Presales: above(sum(SALE),1)

Re: SALES AND PRE SALES

senpradip007 — Thu, 20 Mar 2014 12:30:17 GMT

Hi.

You can use above(Sum(SALE),1) function to get this result.

Re: SALES AND PRE SALES

alexandros17 — Thu, 20 Mar 2014 12:30:52 GMT

Here is an example

Re: SALES AND PRE SALES

its_anandrjs — Thu, 20 Mar 2014 12:33:43 GMT

Hi,

Load your table some thing like below

Temp:

LOAD * Inline

[

YEAR, SALE

2001, 2000

2002, 2500

2003, 4300

2004, 2000

2005, 1000

2006, 5000

2007, 4678

2008, 8623

2009, 7290

2010, 4726

2011, 8564

];

Temp2:

load

YEAR,

SALE,

Previous(SALE) as Presale

Resident Temp;

DROP Table Temp;

Data:

load

YEAR,

SALE,

Presale,

SALE-Presale as diff

Resident Temp2;

DROP Table Temp2;

Hope this helps

Thanks

Re: SALES AND PRE SALES

its_anandrjs — Thu, 20 Mar 2014 12:40:07 GMT

Hi,

Here is updated one lets see

Temp:

LOAD * Inline

[

YEAR, SALE

2001, 2000

2002, 2500

2003, 4300

2004, 2000

2005, 1000

2006, 5000

2007, 4678

2008, 8623

2009, 7290

2010, 4726

2011, 8564

];

Temp2:

load

YEAR,

SALE,

Previous(SALE) as Presale

Resident Temp;

DROP Table Temp;

Data:

load

YEAR,

SALE,

Presale,

SALE-Presale as Diff,

(SALE-Presale) / Presale as Diff%

Resident Temp2;

DROP Table Temp2;

Hope this helps

Thanks & Regards

Re: SALES AND PRE SALES

Fri, 21 Mar 2014 09:08:02 GMT

Thanks ton...!!!!

Re: SALES AND PRE SALES

Fri, 21 Mar 2014 09:08:20 GMT

Thanks ton...!!!!

Re: SALES AND PRE SALES

Fri, 21 Mar 2014 09:08:30 GMT

Thanks ton...!!!!

Re: SALES AND PRE SALES

Fri, 21 Mar 2014 09:11:29 GMT

Hi...!!

Like wise i did as per the below,

TABLE1:

LOAD YEAR,

SALES,

[PROFIT(RS)],

Previous(SALES) AS PRESALES,

SALES - Previous(SALES) AS DIFFRENCE,

(SALES - peek(SALES)) / peek(SALES)*100 as PERCENTAGE

FROM DATA.xlsx

(ooxml, embedded labels, table is SALES);

Suggestion if any .