topic Re: Filtering Based on Expression Results in QlikView

Filtering Based on Expression Results

Tue, 04 Jun 2013 13:10:59 GMT

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

Re: Filtering Based on Expression Results

Anonymous — Tue, 04 Jun 2013 13:14:27 GMT

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

Re: Filtering Based on Expression Results

Tue, 04 Jun 2013 13:59:00 GMT

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

Re: Filtering Based on Expression Results

Anonymous — Tue, 04 Jun 2013 14:04:18 GMT

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

Jonathan

Re: Filtering Based on Expression Results

Tue, 04 Jun 2013 14:10:42 GMT

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

Re: Filtering Based on Expression Results

Anonymous — Tue, 04 Jun 2013 14:21:05 GMT

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

Re: Filtering Based on Expression Results

Tue, 04 Jun 2013 14:38:41 GMT

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

Re: Filtering Based on Expression Results

Anonymous — Tue, 04 Jun 2013 14:58:48 GMT

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

Re: Filtering Based on Expression Results

Tue, 04 Jun 2013 15:11:53 GMT

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?

Re: Filtering Based on Expression Results

Tue, 04 Jun 2013 18:57:54 GMT

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?

Re: Filtering Based on Expression Results

Anonymous — Wed, 05 Jun 2013 11:18:20 GMT

You can ignore filters within set analysis, by using the syntax sum({<{Year=,Month=,Date={'$(SelectedDate)'}>} Field). Set analysis is quite complicated, but there are plenty of references to get you started. However, in this instance the expression you are adding into the list box is not a field that can be manipulated (like Year and Month are in the example).

If you think the value of your calculation, and it's relationship to the various datapoints will not change when filtering happens, then putting the calculation into a script is worthwhile. What you may be able to do is set up a flag that is set up A, B or C.

If you don't want to send out the full application you are building, perhaps you can send the portion of the script in which the RollingSales, TotalSales and Product/Item are defined. From this I may be able to advise you on how to set up such a flag.

Jonathan

Re: Filtering Based on Expression Results

Wed, 05 Jun 2013 12:47:11 GMT

The Item and TotalSales2 are coming in from a SQL table, which I swapped for an inline load.

All that is needed for this calculation is item codes and their total sales.

I did include how RollingSales was calculated see below:

In the script:

I sorted the data by TotalSales2 decending. Then created a cumulative sum of TotalSales2 called RollingSales.

In the chart:

I then created an expression called Cumulative%, which is the RollingSum divided by the Sum Total of all TotalSales2 = RollingSales/sum(Total(TotalSales2))

I then created a second expression called ABC which looked at the Cumulative% and assigned a value of A, B or C depending on an if statement as follows: if((RollingSales/sum(Total(TotalSales2)))<=.8,'A',if((RollingSales/sum(Total(TotalSales2)))<=.95,'B','C'))

However, the above ABC expression changes depending on filters. It would be easiest (I believe) if this calculation was done in the script, but I'm having trouble with the "Total" function which I cannot get to work.

Hope this clarifies, Melanie

Re: Filtering Based on Expression Results

Anonymous — Wed, 05 Jun 2013 15:03:11 GMT

I think you would like to do something like this in the script:

1. Load sales data into Sales table

2. Load products into Products table

3. Left join onto Sales table sum of all sales

4. Left join onto Products table sum of Sales value for each product

5. Left join onto Sales table sum of Sales value for all products from step 4, for each product

6. Left join onto Products table sum of Product Sales divided by sum of all products

7. Left join onto Sales table ABC flag based on calculation from step 6

Left joins where you sum up sales values need to include a GROUP BY clause, to say what you are summing up 'by'. Syntax for some of these steps could be as follows:

3. Left join onto Sales table sum of all sales

LEFT JOIN (Sales) LOAD

SalesKey, // A unique identifier of each sales record

sum(SalesValue) AS Sales_TotalSalesValue

RESIDENT Sales;

4. Left join onto Products table sum of Sales value for each product

LEFT JOIN (Products) LOAD

ProductKey, // The key of the Product that exists on the Sales and Product tables

sum(SalesValue) AS Product_TotalSalesValue

RESIDENT Sales

GROUP BY ProductKey;

5. Left join onto Sales table sum of Sales value for all products from step 4, for each product

LEFT JOIN (Sales) LOAD

ProductKey,

Product_TotalSalesValue AS Sales_TotalProductSalesValue

RESIDENT Products;

6. Left join onto Sales table sum of Product Sales divided by sum of all products, as well as the ABC flag

LEFT JOIN (Sales) LOAD

SalesKey,

Sales_TotalProductSalesValue/Sales_TotalSalesValue AS Sales_Calc

RESIDENT Products;

7. Left join onto Sales table ABC flag based on calculation

LEFT JOIN (Sales) LOAD

SalesKey,

if(Fractile(Sales_Calc,0.8),'A',if(Fractile(Sales_Calc,0.95),'B','C')) AS Sales_CalcFilter

RESIDENT Products;

I hope this is helpful. You will have to tweak it of course, but if it doesn't suit your purposes you might want to repost your question (as seeing this many responses sometimes prevents members viewing posts)

Jonathan

Re: Filtering Based on Expression Results

giordi — Sat, 02 Sep 2017 16:35:10 GMT

I don't believe you use on-the-fly calculation results as the criteria for a filter.

Re: Filtering Based on Expression Results

rimantasendriuk — Fri, 19 Jan 2018 14:41:04 GMT

Try to set "Searchable" for the column (Properties->Presentation). This way user will have to enter some substring for searching and as result of search proper filters will be set on dimensions. It is not so convienent as in case of just cklicking on the value of interest, but you can achieve a goal of filtering the records you are interested in.