topic Re: How to sum the pivoted column in QlikView

How to sum the pivoted column

Anonymous — Thu, 07 Apr 2016 15:16:06 GMT

My data is in the below format

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

I need out put in below format

Output Format
Name	2013	2014	2015	(2013+2015)	(2013+2014)	(2014+2015)
IBM	100	150	350	100+350	100+150	150+350
MS	200	250	300	200+300	200+250	250+300
APPLE	300	300	300	300+300	300+300	300+300

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

Thanks

Re: How to sum the pivoted column

sunny_talwar — Thu, 07 Apr 2016 16:20:28 GMT

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

Re: How to sum the pivoted column

Anonymous — Thu, 07 Apr 2016 16:24:50 GMT

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.

Re: How to sum the pivoted column

sunny_talwar — Thu, 07 Apr 2016 16:38:51 GMT

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)

Re: How to sum the pivoted column

swuehl — Thu, 07 Apr 2016 16:39:30 GMT

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)

Re: How to sum the pivoted column

sunny_talwar — Thu, 07 Apr 2016 16:57:05 GMT

I guess try Stefan's solution then

Re: How to sum the pivoted column

Anonymous — Thu, 07 Apr 2016 18:36:40 GMT

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

Re: How to sum the pivoted column

Anonymous — Thu, 07 Apr 2016 18:41:16 GMT

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)

Re: How to sum the pivoted column

sunny_talwar — Thu, 07 Apr 2016 19:18:24 GMT

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

Re: How to sum the pivoted column

swuehl — Thu, 07 Apr 2016 19:26:17 GMT

...

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)

Re: How to sum the pivoted column

sunny_talwar — Thu, 07 Apr 2016 19:29:43 GMT

Very nice Stefan. How do you think of all these ideas man. I need to start thinking like you somehow

Re: How to sum the pivoted column

swuehl — Thu, 07 Apr 2016 20:07:46 GMT

You are too kind!

This flag multiplication is definitely something to remember, not that it is something new, far from that, it was and is quite common in Qlik world (maybe a bit less used with the arrival of set analysis, though there is an ongoing discussion which is more performant).

If the OP wants more like a front end solution, maybe something using your ValueList() samples could also work here?

(You probably can read my mind...)