Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to sum the pivoted column

My data is in the below format 

  

Name    Year    Sales
IBM2013100
MS2013200
APPLE2013300
IBM2014150
MS2014250
APPLE2014300
IBM2015350
MS2015200
APPLE2015300

I need out put in below format

Output Format
Name201320142015  (2013+2015)(2013+2014)(2014+2015)
IBM100150350  100+350100+150150+350
MS200250300  200+300200+250250+300
APPLE300300300  300+300300+300300+300

I should be able to add the pivoted columns (Year ) .Please help me with logic.

Thanks

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Or

...

Link:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

ReportYear, Year, Flag

2013, 2013, 1

2014, 2014, 1

2015, 2015, 1

2013+2015, 2013, 1

2013+2015, 2015, 1

2013+2014, 2013, 1

2013+2014, 2014, 1

2014+2015, 2014, 1

2014+2015, 2015, 1

2015-2013, 2013, -1

2015-2013, 2015, 1

];

And then just

=Sum(Flag*Sales)

2016-04-07 21_25_44-QlikView x64 - [C__Users_Stefan_Downloads_Community_212500.qvw_].png

View solution in original post

11 Replies
sunny_talwar

Will you ever look at 3 years at a time or this can be more then three year display?

Anonymous
Not applicable
Author

It could be more. Additional column summation is based on business requirement.If we have 2016 year data,end user could ask us to sum 2013+2014+2106 sales.

sunny_talwar

Script:

Table:

LOAD * Inline [

Name, Year, Sales

IBM, 2013, 100

MS, 2013, 200

APPLE, 2013, 300

IBM, 2014, 150

MS, 2014, 250

APPLE, 2014, 300

IBM, 2015, 350

MS, 2015, 200

APPLE, 2015, 300

];

Link:

LOAD * Inline [

ReportYear, Year

2013, 2013

2014, 2014

2015, 2015

2013+2015, 2013

2013+2015, 2015

2013+2014, 2013

2013+2014, 2014

2014+2015, 2014

2014+2015, 2015

];

PivotTable

Dimension:

Name

ReportYear

Expression:

=Sum(Sales)

Capture.PNG

swuehl
MVP
MVP

Maybe like this:

INPUT:

LOAD * INLINE [

Name,    Year,    Sales

IBM, 2013, 100

MS, 2013 ,200

APPLE, 2013, 300

IBM, 2014, 150

MS, 2014, 250

APPLE, 2014, 300

IBM, 2015, 350

MS, 2015, 200

APPLE ,2015, 300

];

TMP:

LOAD DISTINCT Year Resident INPUT;

JOIN

LOAD DISTINCT Year as AsOfYear

RESIDENT INPUT;

AsOf:

NoConcatenate

LOAD Year, IF(Year = AsOfYear,Year, RangeMin(Year,AsOfYear) & '+' & RangeMax(Year,AsOfYear)) as AsOfYear

Resident TMP;

DROP TABLE TMP;

Create a pivot with dimensions Name and AsOfYear and expression =Sum(Sales)

AsOfSum.png

sunny_talwar

I guess try Stefan's solution then

Anonymous
Not applicable
Author

Your logic is working for me but what if I want to subtract 2016 - 2013?

Anonymous
Not applicable
Author

I don't want different combinations.End user will ask for some specific combinations to show (it could be sum of 2016 & 2013 sales or difference of 2016 & 2014 sales)

sunny_talwar

May be this, Script:

Table:

LOAD * Inline [

Name, Year, Sales

IBM, 2013, 100

MS, 2013, 200

APPLE, 2013, 300

IBM, 2014, 150

MS, 2014, 250

APPLE, 2014, 300

IBM, 2015, 350

MS, 2015, 200

APPLE, 2015, 300

];

Link:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

ReportYear, Year, Flag

2013, 2013, 0

2014, 2014, 0

2015, 2015, 0

2013+2015, 2013, 0

2013+2015, 2015, 0

2013+2014, 2013, 0

2013+2014, 2014, 0

2014+2015, 2014, 0

2014+2015, 2015, 0

2015-2013, 2013, 1

2015-2013, 2015, 1

];

Chart's Dimension

Name

ReportYear

Expression:

=RangeSum(

Sum({<Flag = {0}>}Sales),

Sum({<Flag = {1}, Key = {"=SubField(ReportYear, '-', 1) = Year"}>} Sales) - Sum({<Flag = {1}, Key = {"=SubField(ReportYear, '-', 2) = Year"}>} Sales))


Capture.PNG

swuehl
MVP
MVP

Or

...

Link:

LOAD *,

  RowNo() as Key;

LOAD * Inline [

ReportYear, Year, Flag

2013, 2013, 1

2014, 2014, 1

2015, 2015, 1

2013+2015, 2013, 1

2013+2015, 2015, 1

2013+2014, 2013, 1

2013+2014, 2014, 1

2014+2015, 2014, 1

2014+2015, 2015, 1

2015-2013, 2013, -1

2015-2013, 2015, 1

];

And then just

=Sum(Flag*Sales)

2016-04-07 21_25_44-QlikView x64 - [C__Users_Stefan_Downloads_Community_212500.qvw_].png