Change in value

[tracycrown]

Dear all

Kindly advise how to create a report to show change in value for all years.

Data Source:

Description	2011	2012	2013
Revenue	$ 200,000	$            231,400	$              156,822
Cost of sales	$ 140,000	$            161,980	$              109,775
Gross profit	$ 60,000	$              69,420	$                47,047

Requirement :

Description	2012	2011	Change	2013	2012	Change
Revenue	$ 231,400	$    200,000	$       31,400	$ 156,822	$    231,400	$    (74,578)
Cost of sales	$ 161,980	$    140,000	$       21,980	$ 109,775	$    161,980	$    (52,205)
Gross profit	$ 69,420	$ 60,000	$ 9,420	$ 47,047	$ 69,420	$ (22,373)

Thank you

Tracy

[pradosh_thakur]

Hi Tracy

you can remove that part for now. I was trying to make the exact structure you asked for and was thinking to apply some other logic . It is not needed now.

Have a happy new year.

[ghouse449]

Hi thakur,

I am new to qlikview and unable to open your .qvw file . Please send .qvs file to tech.ghouse@gmail.com.

Happy New Year

[ghouse449]

Hi,

Can i get script file of attached application?

[tracycrown]

Dear Mr Pradosh Thakur

Kindly advise why your solution does not work anymore after adding one more field called "Code".

Thank You

Tracy

[pradosh_thakur]

Hi Tracy

No need of any Mr Pradosh . Just Pradosh would be enough .

coming back to your question. It's a cross table to which we are changing to normal table so you need to tweek your code a bit if you add any extra rows which are not in the script before. other code will be exactly same. Only thing you have to add is "code" field in the script.

abc:
CrossTable(YEAR, Data, 2)  
LOAD Code,
    Description,
    [2011],
    [2012],
    [2013]
FROM
[Test-1Aa.xls]
(biff, embedded labels, table is Financial$);

NoConcatenate
abc1:
LOAD Code,Description,num#(YEAR,'####') AS YEAR,Data Resident abc order by Description,YEAR ;

drop Table abc;

LOAD Code,Description,YEAR,Data,IF(PEEK(YEAR)=YEAR-1 AND PEEK(Description) = Description,peek(Data),0) as Prevoius_data,
IF(IF(PEEK(YEAR)=YEAR-1 AND PEEK(Description) = Description,peek(Data),0)=0,'N','Y') AS prev_flag
Resident abc1 order by Code,Description,YEAR;

drop Table abc1;

please find the attached. Its working for me . Let me know if it helps.

[pradosh_thakur]

Hi

It's Pradosh. Welcome to Qlik .

To the updated file tracy sent. Attached is the script.

abc:
Directory;
CrossTable(YEAR, Data, 2)
LOAD Code,
     Description,
     [2011],
     [2012],
     [2013]
FROM
[Test-1Aa.xls]
(biff, embedded labels, table is Financial$);

NoConcatenate
abc1:
LOAD Code,Description,num#(YEAR,'####') AS YEAR,Data Resident abc order by Description,YEAR ;

drop Table abc;

LOAD Code,Description,YEAR,Data,IF(PEEK(YEAR)=YEAR-1 AND PEEK(Description) = Description,peek(Data),0) as Prevoius_data,
IF(IF(PEEK(YEAR)=YEAR-1 AND PEEK(Description) = Description,peek(Data),0)=0,'N','Y') AS prev_flag
Resident abc1 order by Code,Description,YEAR;

drop Table abc1;

[ghouse449]

Hi Pradosh,

how u get that table structure. did u apply any logic for that?

[pradosh_thakur]

pivot table

dimensions  are code description and year

expression are sum(Data) and sum(previous data) and column 1- column 2

dragged and dropped year on top of the measures ..