topic Re: Showing only partial results in QlikView

Showing only partial results

Mon, 31 Mar 2014 20:38:58 GMT

Hi - hopefully this is easy, but I can't quite get it to work:

I have four fields: customer, sales $, margin $ and margin rate.

The customers are grouped together in a set.

Therefore, the sales, margin and rate expressions are for that set.

I want to have the resulting table show me only those customers with margin rate above a certain level and not show me any other results for the specified time period

How do I do that?

I've tried using an if statement in the expression, but that did not limit my results.

Thanks for any help - Matt

Re: Showing only partial results

MK_QSL — Mon, 31 Mar 2014 21:14:37 GMT

Create a variable

vMarginRate = 0.10

Now Create a straight Table

Calculated Dimension

IF(Aggr(SUM([Margin Rate]),Customer)>=$(vMarginRate), Customer

Tick Suppress When Value is NULL

Expression

SUM(Margin Rate)

SUM(Sales)

SUM(Margin)

Or whatever you want

You Can now create an input box with Variable defined above and change the Margin Rate accordingly.

Re: Showing only partial results

Mon, 31 Mar 2014 21:36:05 GMT

Thank you - I'm assuming that's correct, but when I say I'm new to Qlikview, I mean really new. As in: don't know how to create a variable or an input box. I'm sure it's basic stuff, but I'm working off of zero training materials and until now have been doing simple data pulls and manipulating in excel.

Re: Showing only partial results

stevedark — Mon, 31 Mar 2014 22:17:48 GMT

Try expressions like:

if(max([margin rate]) > 0.35, sum([sales $]), 0)

and

if(max([margin rate]) > 0.35, sum([margin $]), 0)

If you have these expressions with a dimension of customer you should only see customers that exist in a set with a margin rate of over 35%.

As Manish says there is merit in creating a variable to store the rate. The easiest way to do this is in your load script:

let vMarginThreshold = 0.35;

This variable can then replace the rate in the expressions above like this:

if(max([margin rate]) > $(vMarginThreshold), sum([sales $]), 0)

You will find a wealth of information if you search Qlik Community (Google search tends to work better than the search box on the site) and there are a number of good blogs out there also.

Hope that helps point you in the right direction.

Steve