Summation of consecutive values.

[#1]

Hello everyone,

Presently I have a problem as following :

I have a excell sheet where Date ,Sales and Flag are three fields.Now Date field contain Date , Sales field contain sales amount.Now if Sales amount is greater than 100000(Sales>100000) then the Flag field will contain value 1 else it would contain 0.The values are shown in the excell sheet. Now - within excell sheet, in Flag field it is required to sum the seven consequtive Flag field which contain value 1. If there are seven consequtive 1 found then, the for each consequtive field in Comment field corresponding to 1 would contain 100,but counting when proceed to 8th value then then Comment field would contain value 50 onwards.

Again if in Flag field 0 value is encountered then then also 50 would be shown in Comment field.

Further if after facing a 0 value, when next 1 value encountered then counting would further started from one and counted normally.

Details about field arre mentioned in excell sheet Test_Sum attached here.

I tried it in both script level and application (.qvw) level.But failed.

Please anyone help me this essential for me.

Thank you all.

[Oleg_Troyansky · Accepted solution]

I'd approach it in the script, with 2 consecutive reloads:

1. Using functions peek() and previous(), you can calculate the cumulative uninterrupted summary of 1 flags.So, if you had 10 rows with the flag = 1, the cumulative total will show 10 in the last row.

2. Then, you can reload it again and use conditional logic to determine whether you add 100 and 50 into the comment field, based on the current value of the flag and the cumulative total of the flag

hope this logic makes sense to all three of you 🙂

[Oleg_Troyansky]

Found a good WIKI article explaining how to use peek() and previous() :

http://community.qlik.com/wikis/qlikview-wiki/using-peek-and-previous-functions-recursively-to-help-transform-data.aspx