Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Try putting the expression that works for you into a list box (see attached).
Jonathan
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
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
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
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
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?
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?