How to create a data filter

[bmensing]

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

[sunny_talwar]

May be something like this:

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

[#3]

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

[johnw]

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.

[johnw]

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.

[hic]

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).

See also https://community.qlik.com/blogs/qlikviewdesignblog/2015/06/08/escape-sequences#comment-33547

HIC

[HirisH_V7]

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

[Mahamed_Qlik]

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

[johnw]

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.

[suepenick]

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