Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filtering Based on Expression Results

I have an expression in my tool that looks at cumulative percent of a code, and then assigns a value of A, B, or C based on that volume.

Something like the below, except Cumluative Perent is in itself a calculation.

If(CumulativePercent<=.8,'A', if(CumulativePercent<=.95,'B',C))

This works great. 

However, I want to be able to create a filter that a user can select, to show only A, B, or C codes (or some combination thereof). 

Additionally, if the user click on B in the straight table, instead of giving me all instances of "b", it gives me just that one item.

Is there any way to create that filter and fix the table?

Thanks, Melanie

14 Replies
Anonymous
Not applicable
Author

Are you doing the if statement you posted in the front end ? Can it be done in the script, or is it dynamic and dependent upon the calculation changing on the front end ?

I suspect you want the generate this A/B/C field on the front end. Try creating a list box, choosing expression from the bottom of the list of fields and adding your expression in to the Edit Expression window that opens up.

Jonathan

Not applicable
Author

I've never taken a Qlikview class, so everything is self taught.


I tried to do it in a script, but my calculation has a sum(total(FIELD)) and the script didn't recognize the "total" even though it did in my expression...

Here is what is working for me as an expression:

if((RollingSales/sum(Total(TotalSales2)))<=.8,'A',if((RollingSales/sum(Total(TotalSales2)))<=.95,'B','C'))

If we can get it so that works in the script, that would be fine.

I tried the list box idea, and it gives me the choices, A, B, C, but when you click on them, they don't relate to the values assigned correctly.

Any other ideas?

Thanks

Anonymous
Not applicable
Author

Try putting the expression that works for you into a list box (see attached).

Jonathan

Not applicable
Author

I did try using the list box, and it didn't work.

As I said above, the list box looked correct, but when you selected "A", it also selected "B" automatically and the values that displayed weren't correct.


There are 800+ A's and 1000+ B's, and 10000+ C's

When I click A using the list box (and I get A,B) there are only 640 records showing and C only has 4000.

Additionally, many of the values are wrong.  I filter for A (get A, B) and it shows C's...

-Melanie

Anonymous
Not applicable
Author

Sorry, missed your statement about trying it in a list box.

What is the dimension that you got this A/B/C expresison working against in a chart ?

You mention that you calulations has a sum(total(FIELD)) in it. You can add the dimension that a Total is being grouped by in to the brackets, in the format total(<FieldName> FieldBeingCalculated)

Can you post a qvw sample ?

Jonathan

Not applicable
Author

What is the dimension that you got this A/B/C expresison working against in a chart ?

Item

You mention that you calulations has a sum(total(FIELD)) in it. You can add the dimension that a Total is being grouped by in to the brackets, in the format total(<FieldName> FieldBeingCalculated)

I tried to do that, but it still didn't accept Total being a valid calculation

Can you post a qvw sample ?

I had to remove 99% of the tool and then create a fake inline table for security reasons, but I kept the issue. 

Thank you for your help.

-Melanie

Anonymous
Not applicable
Author

I can see your expression doesn't aggregate the RollingSum value. If there is no aggregation function used, QlikView will assume you mean only(RollingSum). In this instance you might want to use sum(RollingSum).

Other than that I can't see too much in this sample. for your info, you can scramble dimensions on the Document properties, but I appreciate you may not want to.

A couple more points (I have to go in a sec):

1. You may find there is not enough data available to make such a filter. Once out of the confines of a chart and the dimensions that it is slicing an expression by, what can QlikView use to decide whether a datapoint qualifies as A, B or C

2. It is good to perform such calculatons on the front end, so they are recalculated when filters limit the data. However, you may have more data available for you to perform your test on within the script.

Jonathan

Not applicable
Author

Ideally, I wouldn't want the designation, ABC, to change. 

An item should be assigned an ABC value based on sales, regardless of the other filters that are selected.

The if statement works in the chart, but I want to do the equivalent in the script so it is not variable and so it is filterable.

Is that possible?

Not applicable
Author

Or...

Instead of Scripting, another possible solution is having the expression not update based on filtering.

Is that possible?

Right now, the expression is correct, but when you filter on it (select 'A') it recalculates all the values.  Can Qlikview prevent that somehow?