Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
kkoyyalamudi
Contributor

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
Highlighted
MVP
MVP

Re: How to sum the pivoted column

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
Highlighted

Re: How to sum the pivoted column

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

Highlighted
kkoyyalamudi
Contributor

Re: How to sum the pivoted column

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.

Highlighted

Re: How to sum the pivoted column

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

Highlighted
MVP
MVP

Re: How to sum the pivoted column

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

Highlighted

Re: How to sum the pivoted column

I guess try Stefan's solution then

Highlighted
kkoyyalamudi
Contributor

Re: How to sum the pivoted column

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

Highlighted
kkoyyalamudi
Contributor

Re: How to sum the pivoted column

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)

Highlighted

Re: How to sum the pivoted column

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

Highlighted
MVP
MVP

Re: How to sum the pivoted column

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