Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

samwork1
New Contributor II

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?

Client Name

RevenueMargin $

Margin

%

Revenue change

over last period

A100404020
B503060-10
C7030385
D20010540
E150151530
F20630-2
G104406
H40102540

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.

5 Replies
MVP
MVP

Re: 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?

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

Re: 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?

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)


talk is cheap, supply exceeds demand
MVP
MVP

Re: 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?

Thanks Gysbert! Good catch!

samwork1
New Contributor II

Re: 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?

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>

tonilewisse
New Contributor

Re: 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?

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,

Community Browser