How to select a set of vips

[#1]

Hi guys,

I have a source table as below and what I want to do is to select a set of vips,:

dateid	vipid	orderid	amt
2013	A	1	10
2013	B	1	5
2013	B	2	8
2013	B	3	12
2012	C	1	15
2013	C	2	7
2013	C	3	9
2012	D	3	10
2011	D	2	5
2010	D	1	5

For example, if I select dateid=2013 and orderid=1 then press the button 'select a set of vips', in the Current Selections,the vipid returns A and B, see as bow:

What I need is the vips who purchased in 2013 and did not have their first purchase in 2013, actually this vip is C.

And is it possible to use this button select the vip C?

Thanks a lot.

[#2]

try selectexcluded instead of selectpossible in button properties, let me know is this what you looking for .

attached is file with additional button which selects C

[#3]

Hi Sreevidya,

Thanks for your help, but sorry for my mistake, actually the source table should be as below:

dateid	vipid	orderid	amt
2013	A	1	10
2013	B	1	5
2013	B	2	8
2013	B	3	12
2012	C	1	15
2013	C	2	7
2013	C	3	9
2012	D	3	10
2011	D	2	5
2010	D	1	5

And what I need is the vips who purchase in 2013 and their first purchase are not in 2013, if follow your step, vip C and D will be both selected, see as below:

[#4]

selecting 2013 and orderid as 1 will never allow to select vip C because it's not in possible set with existing selections(dateid 2013, orderid 1)

we have to create a flag in script thought which we can select a dateid 2013 and all vips whose first purchase is not in 2013.

check the file attached,

[jerem1234]

I was able to do what you wanted. If you want to clear some fields afterwards (like dateid or orderid), just add the actions below the action that's there.

PFA

Hope this helps!

[#6]

Hi Sreevidya,

Thanks for your advice, it is really a good idea, but I think for now it is not allowed to add additional script, whatever, thank you very much.

[#7]

Hi,

Thank you for your advice, it works in this scenario, although I am not understand very well,but if it can be more flexible?

For example if what I need is the vips who purchase in 2012 - 2013 and their first purchase are not in 2012 - 2013,

or the vips who purchase in 2013/07 - 2013/10 and their first purchase are not in 2013/07 - 2013/10, does it also woks fine? And how about the performance?

Regards,

[jerem1234 · Accepted solution]

That dashboard currently won't work with more than one year selected. However, I revamped it a bit and the one i am attaching should do it. I created another vipid of F to display the ability of selecting multiple ranges and multiple vipids. Since I don't know your requirements or the purpose of this is for, I am limited in developing the perfect fit for what you're attempting. So Ill try to explain what the formula is doing:

The whole formula is:

='(' & concat({1<orderid=>}distinct if(aggr(min({1<orderid=>}dateid),vipid) < min(total dateid) and aggr(count({1}{<dateid={$(=GetFieldSelections(dateid))},orderid=>}distinct dateid),vipid)>0,vipid),'|')&')'

The breakdown:

aggr(min({1<orderid=>}dateid),vipid) < min(total dateid)

This is finding the minimum date for each vipid (their first purchase) and seeing if it is less then the smallest date selected. It is also ignoring selections made on orderid.

aggr(count({1}{<dateid={$(=GetFieldSelections(dateid))},orderid=>}distinct dateid),vipid)>0

This is finding the number of purchases in the selected range for each vipid. If the number is >0, then they made a purchase in your range. It is also ignoring selections made on orderid.

Together with if:

distinct if(aggr(min({1<orderid=>}dateid),vipid) < min(total dateid) and aggr(count({1}{<dateid={$(=GetFieldSelections(dateid))},orderid=>}distinct dateid),vipid)>0,vipid)

If both those things are true, then get all those distinct vipids that qualify.

concat({1<orderid=>}distinct if(aggr(min({1<orderid=>}dateid),vipid) < min(total dateid) and aggr(count({1}{<dateid={$(=GetFieldSelections(dateid))},orderid=>}distinct dateid),vipid)>0,vipid),'|')

Now concatenate all those vipids into one string,ignoring orderid.

Finally:

='(' & concat({1<orderid=>}distinct if(aggr(min({1<orderid=>}dateid),vipid) < min(total dateid) and aggr(count({1}{<dateid={$(=GetFieldSelections(dateid))},orderid=>}distinct dateid),vipid)>0,vipid),'|')&')'

Add ( and ) to make it work with action of selecting multiple vipids.

PFA

Hope this helps!

[#9]

Dear jerem,

Thanks for your explanation and I think it is very useful for me, thank you very much.