How to sum the pivoted column

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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)

[swuehl]

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)

[sunny_talwar]

I guess try Stefan's solution then

[Anonymous]

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

[Anonymous]

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

[swuehl · Accepted solution]

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)