Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Hi,
Can i get script file of attached application?
Dear Mr Pradosh Thakur
Kindly advise why your solution does not work anymore after adding one more field called "Code".
Thank You
Tracy
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.
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;
Hi Pradosh,
how u get that table structure. did u apply any logic for that?
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 ..