Consecutive counting

wardzynski — Wed, 25 Nov 2020 16:16:04 GMT

Hi Fellow Qlikviewers,

I have created a straight table with different trade results where the dimension is the time stamp or the trade number.

Now I would like to calculate how many consecutive winners (sum(Profit)>0) or losses(sum(Profit)<0) that I have throughout my table. The table is sorted by the trade order.

Later I would like to decide to only show all the trades in the table where we had maximum 2 consecutive losses, which would eliminate all the loosing trades from the table that would occur a potential third time (if I have chosen to display only maximum 2 consecutive losses).

I have outlined in red color in the column "Consecutive Losses" to show you what I would like to see if I select in a variable to ONLY see maximum 2 consecutive losses in my entire table.

Summary:

1. How do I calculate consecutive losses in my table?

2. How do I program a variable and the straigh table so it only show max amount of consecutive losses as I displayed in my attached picture.

I have tried many things, but I can just not figure out how to display the exact amount of maximum consecutive losses that I would like to see.

I am using Qlikview v12.

Re: Consecutive counting

kaushiknsolanki — Tue, 12 Sep 2017 17:26:26 GMT

Try this.

Data:

Load *,If(Values < 0 and Previous(Values) >0,1,

If(Values < 0 and Previous(Values) < 0 ,Peek(Cumm) + 1,0)) as Cumm;

Load * inline [

Date,Values

07/01/2017,10

08/01/2017,20

09/01/2017,-10

10/01/2017,30

11/01/2017,-40

12/01/2017,-50

13/01/2017,20

14/01/2017,30

15/01/2017,-10

16/01/2017,-40

17/01/2017,40

18/01/2017,-10

19/01/2017,50

20/01/2017,60

21/01/2017,-20

22/01/2017,-10

23/01/2017,-30

];

FinalData:

NoConcatenate Load * Resident Data where Cumm > 2;

Drop table Data;

Regards,

Kaushik Solanki

topic Re: Consecutive counting in QlikView

Consecutive counting

Re: Consecutive counting