Can I create an exception report that shows clients on which we have more than less than 20% margin and more than $100,000 of revenue?

[samwork1]

Client Name	Revenue	Margin $	Margin %	Revenue change over last period
A	100	40	40	20
B	50	30	60	-10
C	70	30	38	5
D	200	10	5	40
E	150	15	15	30
F	20	6	30	-2
G	10	4	40	6
H	40	10	25	40

Exception Condition = Show All Clients over X revenue and  Margin less than Y %

Hi if I have the data above in qlik sense and I want to create an exception report where the user can specify the exception condition on the front end... can I do this

Eg the end user would go in and switch X to 100 and y to 30 - the data set would return D and E

Is this possible?

Thanks.

[Oleg_Troyansky]

Yes, you can do it using Advanced Set Analysis, but it's not very easy.

First, you need two variables.

Then, you need an extension to allow user input into the variables.

Finally, you need to construct a Set Analysis expression with an Advanced Search filter that formulates the two conditions that you need:

sum(

{<Customer={"=sum(Sales)>$(vSales) and sum(Margin)/sum(Sales<$(vMargin)"}>}

Value)

That's your solution in a nutshell.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

[Gysbert_Wassenaar]

The expression Oleg posted contains a small typo that will prevent it from doing its magic. It is missing a closing parenthesis in the second sum(Sales):

sum(

{<Customer={"=sum(Sales)>$(vSales) and sum(Margin)/sum(Sales)<$(vMargin)"}>}

Value)

[Oleg_Troyansky]

Thanks Gysbert! Good catch!

[samwork1]

Thanks to all! I won't likely try this for some time but I will

eventually. Will report back.

On Sun, Apr 24, 2016 at 12:32 PM, Oleg Troyansky <qcwebmaster@qlikview.com>

[Anonymous]

Hi Gysbert,

I am really interested in implementing the same as Sam's original query.

sum({<Customer={"=sum(Sales)>$(vSales) and sum(Margin)/sum(Sales)<$(vMargin)"}>}Value)

Is there any chance you could provide an example of how the above could be illustrated in Qlik?

Thanks,