9 Replies Latest reply: May 24, 2016 5:55 AM by Stefan Wühl RSS

    Finding Alternative Values

    Chris Waterworth

      Hi Qlik Community,

       

      I have a bit of a knotty problem.

       

      I have a series of sources each delivering to a number of destinations. Each source has an average cost associated with delivering to a destination.

       

      We want to find the costs of shutting a delivery site and rerouting all the sources to the next lowest best site.

       

      At the moment I have a filter box with the Destinations.

       

      Then a table with Sources list, the current cost to ship to the selected destination and the cost of the next best alternative.

       

      For the next best alternative I currently have this equation:

      min({-1}aggr(avg([Total Cost]),[Destination Name],[Source Name]))

       

      Where the operator {-1} is hopefully removing the current selection so that the minimum value it finds cannot be the current cost.

       

       

      However this is not working, any help would be appreciated.

       

      Cheers,

       

      Chris

        • Re: Finding Alternative Values
          Jonathan Dienst

          {-1} will get the 'next' (I think) value in a chain of previous and next selections (like the Forward button on the toolbar). What you are looking for is probably

           

          {1-$}

           

          which will take all values and remove the current selections.

            • Re: Finding Alternative Values
              Chris Waterworth

              Hi Jonathan,

               

              I'm afraid this is giving me a blank answer and also generating blank answers for the other columns in the table (which is very confusing as it is not a variable)

              • Re: Finding Alternative Values
                Stefan Wühl

                Jonathan, $_1 should return the next selection state, $1 the previous.

                 

                Christopher, could you elaborate a bit more detailed what you have currently selected, and which selections you need to keep and which to revert to the excluded (have a look at the e() function...)?

                  • Re: Finding Alternative Values
                    Jonathan Dienst

                    Yup you're right - that's why I said "I think" (but I thought wrong ). But {1-$} should work if I understand the OP correctly.

                      • Re: Finding Alternative Values
                        Stefan Wühl

                        Wouldn't this also exclude the current selection in the destination?

                        As I understood, the OP just wants to find the next value in a ranked list of cost, grouped by source (or the other way round).

                         

                        Maybe the answer is different, like using the rank argument of min() or using a Rank() function in advanced aggregation to break ties.

                         

                        Just guessing, so Christopher, please clarify.

                          • Re: Finding Alternative Values
                            Chris Waterworth

                            Hi Swuehl,

                             

                            I have currently selected a destination in the filter box for the destination. On the table this generates a list of Source names that are delivering to that destination.

                             

                            Following this thought process I see what you are saying that it would ignore this selection in the Calcs; what would be the best way to get the Qlik to disregard the current delivery route? Some sort of IF statement maybe?

                             

                            Thank you for the help given already from both of you.

                              • Re: Finding Alternative Values
                                Stefan Wühl

                                The second minimal route cost should be returned by

                                 

                                =min( aggr(avg([Total Cost]),[Destination Name],[Source Name]),2)


                                if there are no ties w.r.t. minimum cost between Sources.


                                (BTW, I vaguely remember a similar discussion recently, were you involved in that, too?

                                  • Re: Finding Alternative Values
                                    Chris Waterworth

                                    Hi Swuehl,

                                     

                                    Yes I posted a similar problem a few days ago.

                                     

                                    The problem here is the link between Source Site and Destination is not always the best route, for example we have one source that ships to 6no. different destinations obviously with 5 that are not optimum.

                                     

                                    So because of this the second best option can't be a measure of the alternative route.

                                     

                                    It's quite a tricky problem I'm really trying to wrap my mind around. We need to remove the selected option from reckoning and then look up the best alternative from the remaining (which may be better than the original route.

                                     

                                    Hope I've explained this properly if not please let me know.

                                     

                                    Cheers,

                                     

                                    Chris