Sum of Year and Previous Year in Script - Skip last Year

[#1]

I wanna calculate the Sum of the Sales for one year and the previous for different companies.

However, I do not want a value for 2010 as there is no value to be calculated.

Currently it sums up for example

A, 2015, 100

+

B, 2010, 100

as it seems to go row by row. How can I fix this?

z:

load * inline [

Company, Year, Sales

A, 2010, 100

A, 2011, 110

A, 2012, 120

A, 2013, 130

A, 2014, 140

A, 2015, 100

B, 2010, 100

B, 2011, 110

B, 2012, 120

B, 2013, 130

B, 2014, 140

B, 2015, 100

C, 2010, 100

C, 2011, 110

C, 2012, 120

C, 2013, 130

C, 2014, 140

C, 2015, 100

];

zz:

load Year,

Sales + Previous(Sales) as TwoYearSales

Resident z;

[MK_QSL]

What output you are expecting?

Can you provide it here?

[MayilVahanan]

Hi

Could you please post your expected result.

[#4]

A, 2010, no Data or 0

A, 2011, 210

A, 2012, 220

A, 2013, 250

A, 2014, 270

A, 2015, 240

B, 2010, no Data or 0

B, 2011, 210

B, 2012, 220

B, 2013, 250

B, 2014, 270

B, 2015, 240

C, 2010, no Data or 0

C, 2011, 210

C, 2012, 220

C, 2013, 250

C, 2014, 270

C, 2015, 240

So when I do set analysis subsequently like

avg({<Company,Year ={'2015'}>}[TwoYearSales]) I would get (240+240+240)/3.... Well that is a bad example due to the same sample data, but I am sure you know what I mean.

[MayilVahanan · Accepted solution]

Hi

Try like this

load *, if(Company <> Previous(Company) and Year = 2010, 0, Sales + Previous(Sales)) AS TwoYearSales inline [

Company, Year, Sales

A, 2010, 100

A, 2011, 110

A, 2012, 120

A, 2013, 130

A, 2014, 140

A, 2015, 100

B, 2010, 100

B, 2011, 110

B, 2012, 120

B, 2013, 130

B, 2014, 140

B, 2015, 100

C, 2010, 100

C, 2011, 110

C, 2012, 120

C, 2013, 130

C, 2014, 140

C, 2015, 100

];

[MK_QSL]

Data:
load * inline [
Company, Year, Sales
A, 2010, 100
A, 2011, 110
A, 2012, 120
A, 2013, 130
A, 2014, 140
A, 2015, 100
B, 2010, 100
B, 2011, 110
B, 2012, 120
B, 2013, 130
B, 2014, 140
B, 2015, 100
C, 2010, 100
C, 2011, 110
C, 2012, 120
C, 2013, 130
C, 2014, 140
C, 2015, 100
];


Left Join (Data)
Load Company, Min(Year) as MinYear Resident Data Group By Company;

Final:
Load 
  *, 
  IF(Year = MinYear, Sales) as TempSales,
  IF(Company <> Previous(Company), 0, 
  If(Previous(Year) = MinYear, RangeSum(Peek('TempSales'),Sales), RangeSum(Previous(Sales),Sales))) as TotalSales


Resident Data 
Order By Company, Year;


Drop Table Data;
Drop Fields MinYear, TempSales;

[#7]

take some original data from  and rename fields .

Ur requirement is still not clear.

[varshavig12]

Expression:

if(above(Sales<>null()),Sales+above(Sales),'')

[#9]

Seems like this works for me. I have added a variable for the minimum year as mrkachhiaimp proposed.

However, when I wanna get the average of TwoYearSales for one Company it also considers the zero value for 2010, which I do not want.

Is there a way to say in your expression instead of zero indicate there is no data available, so the average skips this year?

EDIT: Using Null() solved the last issue.

Thanks!

[Anonymous]

Hi ,

Is it like this ?

load * ,

if(Year='2010',0,Sales+Previous(Sales)) as TwoYearSales

inline [

Company, Year, Sales

A, 2010, 100

A, 2011, 110

A, 2012, 120

A, 2013, 130

A, 2014, 140

A, 2015, 100

B, 2010, 100

B, 2011, 110

B, 2012, 120

B, 2013, 130

B, 2014, 140

B, 2015, 100

C, 2010, 100

C, 2011, 110

C, 2012, 120

C, 2013, 130

C, 2014, 140

C, 2015, 100

];