Setting up a marker based on historic data

[aetingu12]

Dear all,

I have 3 year worth of data from (31 Jan 2014 until 31 October 2017) for a list of clients with profits.  I would like to have a marker/flag that would mark the client as 1 if the profit has ever been greater or equal to 1 prior to the date being viewed.

ie if I look at customer A in January 2014 the profit is 5, so expected result is 0. But if I take the same customer, A, in March, I want the flag to say 1 as in February it was equal/or greater than the threshold of 10.  I am sure this could be done within a script, but due to being in a corporate environment this tool is restricted.  Is it possible to get an expression/set analysis solution for this?

As always your help is most appreciated!

Dates	Customer	Profit	Expected result
31-Jan-14	A	5	0
31-Jan-14	B	10	1
28-Feb-14	A	10	1
28-Feb-14	B	5	1
28-Feb-14	C	20	1
31-Mar-14	A	8	1
31-Mar-14	B	10	1
31-Mar-14	C	1	1

[YoussefBelloum]

Hi,

Maybe this as an expression (for the flag):

=if(sum(Profit)>=aggr(Above(sum(Profit)),Dates,Customer), 1, 0)

see attached the QVW

[aetingu12]

Thank you with your reply.  Unfortunately this doesn't work.  I need my Flag to be like the expected result.

If I a look at Customer A in March 2014 the profit is less than 10, but historically it has been >= 10 so it should be flagged as one.

Same thing with C.  In Februrary it >= 10 but in March it is only 1, but the flag should say 1 as it has been above 10.

This flag is going to be a small part of a bigger picture.  I will be using this to identify customers that have ever had profits more than 10 before the period that is being selected.

So I will be running analysis on the customers on January 2017 that have had profits >=10 since the flag has been implemented. 

Hope it makes a bit more sens.

Regards,

Aksel

[aetingu12]

I think I might be over complicating it.  I realise that if I use =if(max(profit),'1','0') then I seem to get the correct flag.  But I think I just need one small little amendment to only have it look at the selection data and prior.

So if I select March 2014, the max statement should be from the first available date up and including March 2014.

Same for 2017;  if I select October 2017, max should only look at all prior dates up and including October 2017 and should not go to November 2017.

Thanks,

Aksel

[YoussefBelloum]

If you use this, you will always have 1 as a flag no matter the selections you made

[aetingu12]

But your data is not giving me the right results.  If you look at your flags, once a customer has reached 10 of profit, it should always remain as 1 as flag.  In your example Customer A flag is 0 in January, which is correct, then in February it should be 1 (as profit is 10) , but you have 0 and in March it should remain as 1(even though the balance went down).

[YoussefBelloum]

I'm aware that i gave you incomplete answer

[aetingu12]

I am afraid I just don't see it. I mean, I do not understand what you are trying to do with the Above and Aggr functions in terms of the logic.