topic Re: How to create a data filter in QlikView

How to create a data filter

bmensing — Wed, 25 Nov 2020 16:16:04 GMT

Hello there,

have 4 dimensions,

1) date

2) customer

3) order number

4) order value

i want to sum up the positive order values of the customer Coca-Cola for example and i dont want to use a list box as an filter.

can you help me out?

best regards

Re: How to create a data filter

sunny_talwar — Wed, 27 Apr 2016 15:43:59 GMT

May be something like this:

Sum({<customer = {'Coca-Cola'}, [order value] = {'>0'}>}[order value])

Re: How to create a data filter

Wed, 27 Apr 2016 15:53:04 GMT

create a new field in the script by loading the positive values like

if(left([order value], 1) = '-', null(), [order value]) as [order value new]

and use the [order value new] for the calculation

Re: How to create a data filter

johnw — Wed, 27 Apr 2016 16:20:31 GMT

This is a total aside, but it bothers me that '>0' works as a search expression. Is there anywhere else in QlikView that a single quote does NOT mean a text literal? Maybe there is, but I'm not thinking of one right now. So if I didn't know better, I'd expect this to be looking for a literal value of '>0' rather than looking for numbers greater than zero.

But it's probably user friendly to be flexible. As best I can tell, QlikView is doing something like if there IS a literal matching that value, it assumes you meant that literal and returns only that literal. If there isn't, then it interprets it as a mathematical comparison. And even if I have both numeric values and '>0' in my list of values, and I want to search for values greater than zero, I can always write it in some other way, such as '>+0'.

Sorry, a bit off your topic, Bernd. Sunny's answer looks right to me, though.

Re: How to create a data filter

johnw — Wed, 27 Apr 2016 16:30:41 GMT

If you don't match an if and don't specify an else, it returns null, so this would be a shorter equivalent:

if(left([order value], 1) <> '-', [order value]) as [order value new]

And since order value is almost certainly numeric since we intend to sum it, and since we want Coca-Cola, something more like:

if([customer]='Coca-Cola' and [order value]>0,[order value]) as [positive Coca-Cola order value]

I usually prefer script solutions to set analysis solutions where both seem viable, so I sometimes do things like this. If this case is quite special, or if it's used in multiple places, then I'd probably put it in script. I prefer my complexity be in script rather than my charts. It makes for simpler expressions and I suspect executes marginally faster. But if there are a lot of positive Coca-Cola order values, this could eat up some memory that the set analysis solution wouldn't. And if you have to add this field with, say, a join of the table to itself that you wouldn't otherwise have, and then you didn't have a unique ID to join on, so you added one, now your script is going somewhat slower and you're using even more memory. So there are trade offs to consider. Though I suppose in most applications, there's not so much data for these trade offs to be of much concern, and it comes down to whatever's easiest and clearest.

Re: How to create a data filter

hic — Thu, 28 Apr 2016 07:45:12 GMT

John

You are spot on. The fact that '>0' works as a search, and not as a literal match, is a bug. It will be fixed one day, hopefully... Searches should have double quotes (or square brackets, or grave accents).

HIC

Re: How to create a data filter

HirisH_V7 — Thu, 28 Apr 2016 08:12:28 GMT

Hi ,

May be like this,

Order_Data:
LOAD *,
If([order value]<=0,'Negative','Postive') as OrderFilter,
[order value] as od
INLINE [
    date, customer, order number, order value
    22/04/2016, Coca-Cola, 1, -30
    23/04/2016, Coca-Cola, 2, -20
    24/04/2016, Coca-Cola, 3, 10
    25/04/2016, Coca-Cola, 4, 20
    26/04/2016, Coca-Cola, 5, 200
    27/04/2016, Coca-Cola, 6, 444
    28/04/2016, Coca-Cola, 7, -900
];

By creating a orderfilter you can select Positive or negative order value at front end,like this

or in set analysis,

Sum({<OrderFilter={'Postive'}>}[order value])

HTH,

PFA,

Hirish

Re: How to create a data filter

Mahamed_Qlik — Thu, 28 Apr 2016 11:58:09 GMT

Hi Bernd,

Rather using direct list box for the order value, It is better you can make it as calculation dimension.

In expression, you can change the order value as per your requirement.

Regards,

mahamed

Re: How to create a data filter

johnw — Thu, 28 Apr 2016 15:43:44 GMT

Then I'm glad I've always followed the convention that single quote means literal, double quote means search. So if you fix it, hopefully none of MY applications stop working. But this is where backwards compatibility rears its ugly head, and suddenly a whole lot of people's applications stop working when you fix the bug. It's one of those bugs that it might be better to never fix. Not my call to make, of course.

Re: How to create a data filter

suepenick — Mon, 23 May 2016 12:40:44 GMT

I love putting flags in load scripts for this type of thing. It keeps the refresh and click speed up.

in the load script put

if(customer = 'Coca-Cola',1,0) as CokeFlag

then in the expression

sum(if([order value]>0, [order value] * CokeFlag, 0))

this will only give you coke's order value when greater than 0

simple and quick loading, never underestimate the value of the dash board refresh speed when the user is clicking on selections.

Flags are speedy in expressions inside the dashboards.

also - they work great on buttons