topic Re: Change in value in QlikView

Change in value

tracycrown — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Change in value

tracycrown — Sun, 31 Dec 2017 06:10:52 GMT

Dear all,

I do not expect any response on new year day but I would like to wish everyone a Happy, Healthy and Prosperous New Year 2018.

Best Wishes

Tracy

Re: Change in value

mdmukramali — Sun, 31 Dec 2017 06:26:21 GMT

Hi,

Kindly find the attached Application.

I hope it will help you.

Re: Change in value

mdmukramali — Sun, 31 Dec 2017 06:39:35 GMT

Hi ,

PFA updated File.

I would like to wish everyone a Happy, Healthy and Prosperous New Year 2018.

Thanks & Wish you the same.

Re: Change in value

tracycrown — Sun, 31 Dec 2017 08:15:24 GMT

Dear Mohammed Mukram

Is there a better way as one needs to hard code the year (2012, 2013 ... 2017 ) in future ?.

Thank you

Tracy

Re: Change in value

shiveshsingh — Sun, 31 Dec 2017 08:18:41 GMT

Happy new year to you too..please refer attached

test:

CrossTable(Year, Value)

LOAD Description,

[2011], [2012],

[2013]

FROM

[..\Desktop\Test-1.xls]

(biff, embedded labels, table is Financial$);

load

Description,Year,Value,if(Description=Previous(Description),Previous(Value),0) as Previuos_Old,

Previous(Value)

Resident test;

Re: Change in value

mdmukramali — Sun, 31 Dec 2017 08:34:36 GMT

Hi,

Kindly find the Updated attached Application.

Re: Change in value

tracycrown — Sun, 31 Dec 2017 16:09:26 GMT

Dear Mr Shivesh Singh

Happy New Year 2018

You are using straight table, please advise how to change to pivot table ?.

Thank you

Tracy

Re: Change in value

pradosh_thakur — Sun, 31 Dec 2017 18:22:33 GMT

check out if this helps.

Re: Change in value

tracycrown — Mon, 01 Jan 2018 00:44:34 GMT

Dear Mr Pradosh Thakur

Happy New Year 2018 to you and your family.

Thank you for your kind advice, please advise what is the purpose of INLINE below :

LOAD * INLINE [
DIM
1
2];

Thank You

Tracy

Re: Change in value

pradosh_thakur — Mon, 01 Jan 2018 03:53:49 GMT

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.

Re: Change in value

ghouse449 — Mon, 01 Jan 2018 05:12:07 GMT

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

Re: Change in value

ghouse449 — Mon, 01 Jan 2018 10:58:50 GMT

Hi,

Can i get script file of attached application?

Re: Change in value

tracycrown — Tue, 02 Jan 2018 03:15:19 GMT

Dear Mr Pradosh Thakur

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

Thank You

Tracy

Re: Change in value

pradosh_thakur — Tue, 02 Jan 2018 03:49:41 GMT

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.

Re: Change in value

pradosh_thakur — Tue, 02 Jan 2018 03:54:20 GMT

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;

Re: Change in value

ghouse449 — Tue, 02 Jan 2018 07:24:02 GMT

Hi Pradosh,

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

Re: Change in value

pradosh_thakur — Tue, 02 Jan 2018 17:20:09 GMT

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 ..