Difference between two values in pivot table

LION KING
LION KING used Ask the Experts™
on
Qlikview Help!!!
I have a pivot table in Qlikview 11.
1) Original - is my original pivot table
2) Final - is the pivoted version of Original
3) Result - this is what the result needs to be

How to calculate the difference columns - DIFF1, DIFF2, DIFF3 in the same format as attached? Please help.

Capture2.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Are you using a query for pivot table ? Which Database are you using ?
LION KINGDeveloper

Author

Commented:
Yes, Pawan. I'm using a query (load script) in Qlikview. SQL Server 2016 is the database.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please provide me the query. Will modify that for you to the required result. Also add SQL Server as a Topic for future reads.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

LION KINGDeveloper

Author

Commented:
ODBC CONNECT TO TEST_SQL_SERVER;
CUR_QTR:
LOAD
      NAME,
      DATE1,
      COMPANY,
      AMT,
      LEFT(DATE1,4)             AS YR,
         RIGHT(DATE1,2)             AS MTH
;
SELECT *
FROM CUR_QTR;

JOIN(CUR_QTR)
LOAD
NAME,
      DATE1,
      COMPANY,
      AMT,
      LEFT(DATE1,4)             AS YR,
         RIGHT(DATE1,2)             AS MTH


FROM
[C:\TEST.xlsx]
(ooxml, embedded labels, table is Sheet1);

This is the query from Qlikview. CUR_QTR is the table from SQL server that has data for 201703 only. For prior period the data is loaded from Excel file. While creating the pivot table in QLikview, this script is used to create dimensions n measures. Measure is the sum(Amt).
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Are you doing pivot in SQL server or in QlikView?
LION KINGDeveloper

Author

Commented:
Doing Pivot in Qlikview - within Pivot table (chart).
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
try Like this... (Never did that in UI though)

DIFF1 : {201703} - {201612}
LION KINGDeveloper

Author

Commented:
I tried that earlier. That didn't work. It also creates column under each year month.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Please find FULL TESTED SOLUTION.

Table Creation and DATA Insertion.

CREATE TABLE testPivot1
(
	 Name VARCHAR(10)
	,yr_mth VARCHAR(6)
	,TE VARCHAR(10)
	,Company INT
)
GO

INSERT INTO testPivot1 VALUES 
('ABC',201606,'',1100),
('ABC',201609,'',1200),
('ABC',201612,'',1200),
('ABC',201703,'',900),
('DEF',201606,'',300),
('DEF',201609,'',400),
('DEF',201612,'',400),
('DEF',201703,'',200)
GO

Open in new window


SOLUTION

;WITH CTE AS
(
	SELECT *, CONCAT('DIFF',ROW_NUMBER() OVER(PARTITION BY Name ORDER BY yr_mth)) rnk FROM
	(
		SELECT * , Company - LAG(Company) OVER (PARTITION BY Name ORDER BY yr_mth) DIFF
		FROM testPivot1
	)u
)
SELECT Name,MAX([201606]) [201606] ,MAX([201609]) [201609] ,MAX([201612]) [201612] 
,MAX([201703]) [201703],MAX([DIFF2]) [DIFF2] ,MAX([DIFF3]) [DIFF3]  ,MAX([DIFF4]) [DIFF4]
FROM 
CTE r
PIVOT 
( 
	MIN(r.Company)
	FOR [yr_mth] IN ([201606],[201609],[201612],[201703]) 
)t
PIVOT 
( 
	MIN(DIFF)
	FOR [rnk] IN ([DIFF2],[DIFF3],[DIFF4]) 
)t1
GROUP BY Name 

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
Name       201606      201609      201612      201703      DIFF2       DIFF3       DIFF4
---------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
ABC        1100        1200        1200        900         100         0           -300
DEF        300         400         400         200         100         0           -200

(2 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned.
Provided tested solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial