12 Replies Latest reply: Jun 5, 2013 11:03 AM by Jonathan Brough RSS

    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

        • Re: Filtering Based on Expression Results
          Jonathan Brough

          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

              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
                  Jonathan Brough

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

                  Jonathan

                    • Re: Filtering Based on Expression Results

                      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
                          Jonathan Brough

                          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

                              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
                                  Jonathan Brough

                                  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

                                      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

                                          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
                                              Jonathan Brough

                                              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

                                                  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
                                                      Jonathan Brough

                                                      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