Using a Variable or "Set Analysis" as a dimension filter - Customer Retention

[basildur]

Good Time of Day to you Qlik Gurus,

I've been working with Qlik for a few month and have got a question to ask you guys on the use of variables and filter selection as well as set analysis, so here I go.

This is my current Customer Retention App, with the main focus on the 4 manually defined groups of Customers, namely:

a) Lost

b) Contracted

c) Expanded

d) Gained

I define these statuses in a lengthy Set Analysis:

=if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]>}[Containers]) > vMinContainers

    and SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]), [Customer combi]),[Customer combi]))

, 'Expanded',

if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]>}[Containers]) > vMinContainers

and SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) <= SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]), [Customer combi]),[Customer combi]))

    ,'Contracted',

if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) > vMinContainers

and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]) <=0 , [Customer combi]),[Customer combi]))

,'Gained',

if(Match([Customer combi],aggr(if(SUM({$<[Date]=p([Current Date]), [Current MonthPeriod]=>}[Containers]) <=0

and SUM({$<[Date]=p([Previous Date]), [Previous MonthPeriod]=>}[Containers]) > vMinContainers , [Customer combi]),[Customer combi])),

'Lost'))))

And it works fine:

(I use Measure Variables for Status groups in bar charts and due to different dimensions there, they also can't be clicked to select a separate status)

What I want to do here, rather then making a selection in my table, I would like to have a drop down above the table to be able to select each or any of these predefined categories, so all the the other elements of the app will only display the info of selected category as per below:

I hope I am making myself clear, otherwise please let me know if I am not clear.

Thanks in advance,

Vlad

[sunny_talwar]

Would you be able to share a sample qvf to look at this and play around with it a little bit

[basildur]

Hi Sunny, this being built on the server, rather then desktop application and it would be at least 150 Mb if I exported it due to sheer amount of data. Also, I wouldn't like to share commercially sensitive information.

Let me know if you would like me to explain myself or some elements in more detail, or perhaps share the scripts or Set Analyses.

[sunny_talwar]

Yes, explanation would be helpful and may be share some mock up data with the output you expect from the mock up data