Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to select a set of vips

Hi guys,

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

dateidvipidorderidamt
2013A110
2013B15
2013B28
2013B312
2012C115
2013C27
2013C39
2012D310
2011D25
2010D15

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:

222.jpg

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.

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Re: How to select a set of vips

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!







View solution in original post

8 Replies
Highlighted
Not applicable

Re: How to select a set of vips

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

Highlighted
Not applicable

Re: How to select a set of vips

Hi Sreevidya,

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

dateidvipidorderidamt
2013A110
2013B15
2013B28
2013B312
2012C115
2013C27
2013C39
2012D310
2011D25
2010D15

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:

12.jpg

Highlighted
Not applicable

Re: Re: How to select a set of vips

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,

Highlighted
Specialist II
Specialist II

Re: Re: How to select a set of vips

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!

Highlighted
Not applicable

Re: How to select a set of vips

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.

Highlighted
Not applicable

Re: How to select a set of vips

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,

Highlighted
Specialist II
Specialist II

Re: Re: How to select a set of vips

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!







View solution in original post

Highlighted
Not applicable

Re: How to select a set of vips

Dear jerem,

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