condition in Pivot Table between 2 dimensions

[louisernould]

Hi the Qlik Community,

I've a problem about something, and I hope you can help me to solve it ?

I have an excel file, and in the file, there are somes dimensions : the Date, a reference ,an evolution of stock, and some modifications about this stock (Sell and rectification).

I'm looking for a formula which can tell me if a modification on the stock put the stock under Zero during 1 week.

for example, I have this king of table:

Date	Reference	Stock	Rectification	sells
01/08	refA	20	0	0
02/08	refA	20	-10	0
03/08	refA	10	0	5
04/08	refA	5	0	7
05/08	refA	-2	0	0
01/08	refB	20	0	0
02/08	refB	20	-10	0
03/08	refB	10	0	20
04/08	refB	-10	0	5
05/08	refB	-15	0	0

I'm looking for something like that:

Date	reference	Binairy
05/08	refA	1
04/08	refB	1
05/08	refB	1

I already search with getFieldSelections, and some set analysis but it's not helpfull...

if anyone has a great idea, it will be so nice !!

thank you in advance

Louis

[MindaugasBacius]

I have two solutions:

1. With flag that is created in the load sentence;

2. Without the flag with data we have.

Please take a look at the attachment.

[MarcoWedel]

maybe

LOAD *,
          -(Stock<0) as Binary
From Yourtable;

?

hope this helps

regards

Marco

[louisernould]

Hi Marco and Mindaugas,

First, thank for your quick answers, you're working faster than me

Both of your solutions work to see how many reference has a stock <0. But I'm looking for the condition which can tell me if I have a stock <0 And a rectification  > 0 (at least one) a week before.

for this example, it will return nothing : I have a Stock <0 on 05/08, but no rectification.

Date	Reference	Stock	Rectification	sells
01/08	refA	10	0	0
02/08	refA	10	0	0
03/08	refA	10	0	5
04/08	refA	5	0	7
05/08	refA	-2	0	0

Otherwise, for this example,

Date	Reference	Stock	Rectification	sells
01/08	refA	10	0	0
02/08	refA	10	10	0
03/08	refA	0	0	5
04/08	refA	-5	0	7
05/08	refA	-12	0	0

It will return

Date	reference	Binairy
04/08	refA	1
05/08	refA	1

Hope your can still help me

regards,

Louis

[Or]

Perhaps something along the lines of:

If(Stock < 0 AND aggr(sum(Rectification,Reference))>0,1) ?

This isn't specific to one week - it will check your entire selected date population. If you need it to specifically check one week back, you'll need to look into a range function / inter-record function instead.