Value based on sum of previous columns

[Par123]

Hi All, 

I need to create a tabular chart. 

I have columns in month year format like Jan-2017,Feb-2017.....Jan 2018, Feb 2018 and so on. 

My result should be like

In Jan 2017, suppose the value is 1 .

In Feb 2017, it should check the value of Jan 2017 and if that is greater than zero or not.

If > 0 then put 0 else some value (already calculated that in a different column) 

But in Mar 2017, it should check the sum of jan and feb and check if that is greater than zero or not. 

In April 2017, it will be checking the sum of jan,  feb and mar and so on. 

But now in Jan 2018, it will be checking the sum from feb 2017 to dec 2017 and check if that is greater than zero or not.

And so on. 

Please let me know if my question is clear and if it is then kindly suggest some approach. 

I have tried to do it at chart level but unable to do so, now trying at script level. 

Thanks

[Par123]

Hi Arthur, 

Sorry for late reply.

Thanks for the approach and above explanation. It really helped me a lot.

My question is

When the year changes means when it comes to Jan 2018 its considering as row num 1.

I understood the code but when year changes then it should consider the previous year as well. 

For example: for jan 2018, it should be taking the sum of last 11 months, from feb 2017 to dec 2017.

But it is considering it as row num 1 and calculating the cumValue using value of jan 2018.

Can you please suggest something regarding this.

Thanks

[Arthur_Fong]

If that is the case, please update your expected result again.

[Arthur_Fong]

For Feb 18 - ABC, result should be 0 since there is data for past 11 months [Mar-17 to Jan-18]

Your expected result is recorded as 1.

[Par123]

Hi Arthur,

Thanks for the reply.

for ABC, in feb 2018 I have mentioned this excel formula.

=IF(SUM(D3:N3)>0,0,IF(DATA!O4>0,1,0))

if D3 to N3 which means if sum of mar 2017 to jan 2018 is greater than 0 then value 0 else it will check the DATA sheet for the value of feb 2018.That is why in my expected result the value is 1.

The same above formula applies to the rest of the months, which are calculating the sum of last 11 months.

Thanks

[Par123]

Any suggestions on this?

[Arthur_Fong]

Try this:

temp: 
load * inline [
CompanyName,Jan-17,Feb-17,Mar-17,Apr-17,May-17,Jun-17,Jul-17,Aug-17,Sep-17,Oct-17,Nov-17,Dec-17,Jan-18,Feb-18,Mar-18,Apr-18,May-18,Jun-18,Jul-18,Aug-18,Sep-18,Oct-18,Nov-18,Dec-18
XYZ,2,2,0,3,4,0,1,5,6,5,1,0,1,2,2,5,0,1,2,1,2,0,1,0
ABC,0,2,0,3,0,5,1,5,6,5,0,0,0,2,2,5,0,1,2,0,2,0,1,0
];

CrossTable(MonthYear,Value,1) //pivot the table into a proper structured table
tempRaw:
load * resident temp;

drop table temp;

NoConcatenate //Convert MonthYear text into date format
Raw:
load
Date#(MonthYear,'MMM-YY') AS MonthYear,
DATE(Date#(MonthYear,'MMM-YY'),'YYMM') AS YearPeriod,
Value,
CompanyName
Resident tempRaw;
drop table tempRaw;

for i=1 to 12 //Logic for Past11Months Value
join(Raw)
load
Value as Past$(i)Month,
CompanyName,
AddMonths(YearPeriod,$(i)) as YearPeriod
resident Raw;
next i
//exit SCRIPT;
tempRaw:
load alt(Past1Month,0)+alt(Past2Month,0)+alt(Past3Month,0)+alt(Past4Month,0)+alt(Past5Month,0)+alt(Past6Month,0)
+alt(Past7Month,0)+alt(Past8Month,0)+alt(Past9Month,0)+alt(Past10Month,0)+alt(Past11Month,0)+alt(Past11Month,0) as CumValue,
Value,
CompanyName,
YearPeriod,
MonthYear
resident Raw
where right(YearPeriod,2)<13
and not(isnull(MonthYear))

;
drop table Raw;
//EXIT SCRIPT;
NoConcatenate //Sort data based on date column
Raw2:
load MonthYear,
CompanyName,
Value,
CumValue,
YearPeriod
Resident tempRaw
order by
CompanyName,
MonthYear;

drop table tempRaw;
//exit script;

Raw:
load *,rowno()as Row2,if(RowNo()=1 and Value<1,0,if(CumValue>1 and RowNo()<>1,1,0)) as Result;
load
MonthYear,
Value,
CompanyName,
YearPeriod,
CumValue
resident Raw2;

drop table Raw2;

for i=1 to 12
join(Raw)
load
Result as Past$(i)MonthResult,
CompanyName,
AddMonths(YearPeriod,$(i)) as YearPeriod
resident Raw;
next i

tempRaw:
load *,if(peek(CumResultFlag)=CumResultFlag and CumResultFlag=1,0,CumResultFlag) as CumResult;
load
if(RowNo()=1,if(Value>0,1,0),if(alt(Past1MonthResult,0)+alt(Past2MonthResult,0)+alt(Past3MonthResult,0)+alt(Past4MonthResult,0)+alt(Past5MonthResult,0)+alt(Past6MonthResult,0)
+alt(Past7MonthResult,0)+alt(Past8MonthResult,0)+alt(Past9MonthResult,0)+alt(Past10MonthResult,0)+alt(Past11MonthResult,0)+alt(Past11MonthResult,0)>0,if(Past11MonthResult=1,1,0),if(Value>0,1,0))) as CumResultFlag,
Value,
CompanyName,
YearPeriod,
MonthYear
resident Raw
where right(YearPeriod,2)<13
and not(isnull(MonthYear));

drop table Raw;

exit SCRIPT;

Do exit script at each ';' to understand what is happening.

Thanks and regards,

Arthur Fong