topic Re: Setting up a marker based on historic data in QlikView

Setting up a marker based on historic data

aetingu12 — Mon, 27 Nov 2017 08:36:45 GMT

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

Re: Setting up a marker based on historic data

YoussefBelloum — Mon, 27 Nov 2017 09:01:24 GMT

Hi,

Maybe this as an expression (for the flag):

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

see attached the QVW

Re: Setting up a marker based on historic data

aetingu12 — Mon, 27 Nov 2017 09:19:03 GMT

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

Re: Setting up a marker based on historic data

aetingu12 — Mon, 27 Nov 2017 09:32:41 GMT

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

Re: Setting up a marker based on historic data

YoussefBelloum — Mon, 27 Nov 2017 09:37:15 GMT

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

Re: Setting up a marker based on historic data

aetingu12 — Mon, 27 Nov 2017 09:45:02 GMT

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).

Re: Setting up a marker based on historic data

YoussefBelloum — Mon, 27 Nov 2017 09:46:27 GMT

I'm aware that i gave you incomplete answer

Re: Setting up a marker based on historic data

aetingu12 — Mon, 27 Nov 2017 11:27:06 GMT

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.