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

[Arthur_Fong]

Do you have a sample app?

[Par123]

I have an excel file in which the formulas are there on the cells. Those formulas I need to apply in qlikview.

Do you want that? 

[Arthur_Fong]

Your data starting from Jan-2017?

Can you provide some sample data, together with the expected output result?

[Par123]

PFA excel file which contains sample data and expected result sheets.

[Arthur_Fong]

There you go:

First, you need to convert your table into QV readable thin-long format table using crosstable function.

Next, sort the new table by CompanyName and YearMonth.

Then, sum up the value according to Year and CompanyName using peek() function.

Finally, create flags according to the logic you defined.

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

[Par123]

Hi Arthur,

Thanks a lot for your help.

I am not able to open the attached .qvw file. Can you please share the script.

Thanks

[Arthur_Fong]

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,
Value,
CompanyName,
AutoNumber(MonthYear,CompanyName&Year(Date#(MonthYear,'MMM-YY'))) as RowNum
Resident tempRaw;
drop table tempRaw;

NoConcatenate //Sort data based on date column
Raw2:
load MonthYear,
CompanyName,
RowNum,
Value
Resident Raw
order by
CompanyName,
MonthYear;

drop table Raw;

Raw:
load *,if(RowNum=1 and Value<1,0,if(CumValue>1 and RowNum<>1,0,1)) as Result;
load
MonthYear,
Value,
CompanyName,
RowNum,
if(RowNum=1,Value,peek(Value)+peek(CumValue))as CumValue
resident Raw2;

drop table Raw2;

[Par123]

Thanks for the script.

One question please..

In the below line,

if(RowNum=1,Value,peek(Value)+peek(CumValue))as CumValue

you have calculated peek(CumValue) first and then provided CumValue as alias. How?

[Arthur_Fong]

if(RowNum=1,Value,peek(Value)+peek(CumValue))as CumValue

The logic flows like this:

1. Because of the first if statement, if(RowNum=1), QV will assign Value as CumValue. This line will create the new column CumValue for the first row.
2. When comes to second row, if(RowNum=1) is no longer valid, the logic will flow through the second statement, this will peek the first row CumValue.

Thanks and regards,

Arthur Fong