- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to sum the pivoted column
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Will you ever look at 3 years at a time or this can be more then three year display?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I guess try Stefan's solution then
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your logic is working for me but what if I want to subtract 2016 - 2013?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- « Previous Replies
-
- 1
- 2
- Next Replies »