3 Replies Latest reply: Jan 29, 2014 7:03 PM by Lyndon Nelson RSS

    Using a calculated value in below() to create a different size sliding window in a chart

    Lyndon Nelson

      We are having a terrible time trying to get a sliding window of days (which can vary for each row in the table) to calculate efficiently. We have been able to get it to calculate correctly using multiple IF statements, it will not scale well up to a maximum of 1097 IF statements. The attached example demonstrates a couple ways of using the inefficient IF statements (which display the correct answer), and what I think would be the desired expression (which returns the correct answer).

       

      The short version of the problem is that I need the expression for OnBuff to read and use the OnTRR value in the below() statement (see example.qvw). The displayed value in the OnBuff column (which is incorrect about half the time) should equal the value in both the OnBuff99 and OnBuffTest columns (which are correct in every instance). I have tried every way I can think of to call the value in the OnTRR column, but can never get it to display the correct answer in OnBuff for every row.

       

      Use the Test1 bookmark and go to the Buffer sheet to see the output data, although you can pick a single Site with a handful of PartNos and you should see the problem. Ignore the results for any OnTRR that is over 60, as I didn’t put IF statements in for over that number because it slows the app down too much.

       

      More information for those who want it.

       

      The client needs to calculate the buffer for 1000 part numbers, and the value of the OnTRR can be as high as 1097. This makes a multiple layer of IF statements very inefficient, at least as I have them currently written. I have tried many different combinations of variables, IF statements, Set Expressions, dollar expansions, etc, but just can’t seem to get anywhere close to 1000 part numbers to return an answer in any reasonable time frame.

       

      I have limited the OnBuff99 and OnBuffTest IF statements to use an OnTRR of 99 and 60 respectively to prevent the app from becoming extremely slow. Even right now it takes close to a minute to calculate for 40 part numbers which means taking about 25 minutes to calculate for 1000 part numbers. But that’s not all – the production app will also need to calculate an OffBuff and an AggBuff using OffTRR and AggTRR (which aren’t shown here but will use the same type of expression). That pushes the time to over an hour for the answer to return which isn’t acceptable.

       

      A side note about the locked ShowZeroPoints? Listbox – for OnBuff to calculate correctly, we need to have a row returned for every date even if the QTY is zero so we examine the OnTRR number of calendar days. So we use the Set Expression in the OnBuff calculation to get a QTY for all days, but have it locked to N so that standard expressions don’t see all the extra data.

       

      Hope this all makes sense – we could really use your help on this as it has us puzzled. If you think you have an answer, please incorporate it into the example.qvw and send it back – I will check it out in the production app to see if the performance is better.

       

      Thanks.

        • Re: Using a calculated value in below() to create a different size sliding window in a chart
          Sven Uhlig

          Hi,

           

          please try:

           

          max(
              AGGR(
                  RANGESUM(BELOW(
                      SUM({<EXCLUDE_RECORD={''},STATION={'ON-STATION','All'},ShowZeroPoints? = {'Y'}, THREE_YEAR= {'-1'}>}QTY)
                      ,0
                      ,
                      
                      Min( {<EXCLUDE_RECORD={''},STATION={'ON-STATION'},ONE_YEAR= {'-1'}>} total<PARTNO>
                          aggr(
                              if(
                                  Min({<EXCLUDE_RECORD={''},STATION={'ON-STATION'},ONE_YEAR= {'-1'}>} TRR_DAYS) >= fractile({<EXCLUDE_RECORD={''},STATION={'ON-STATION'},ONE_YEAR= {'-1'}>} total <PARTNO, SITE> TRR_DAYS, .9)
                                , Min( {<EXCLUDE_RECORD={''},STATION={'ON-STATION'},ONE_YEAR= {'-1'}>} TRR_DAYS)
                                 )
                          , PARTNO, SITE, STATION, TRR_DAYS )
                      )
                  )
                  )
              ,PARTNO,MASTER_CAL_KEY)
          )
          

           

          which seems to work for me.

          You will need to "break out" of outer aggr (total<PARTNO>).

           

          Regards

           

          Sven

          • Re: Using a calculated value in below() to create a different size sliding window in a chart
            Sven Uhlig

            Hi,

             

            Sorry for deleting your response, by deletion of my double post

             

            Afaik you can't reference an other column within an aggr-expression, it's just living in an other context.

            The same thing applies to variables, which are evaluated at the beginning of the calculation and don't work as you would expected, within the context of a row.

             

            The second thing which should be mentioned: aggr(below(... depends on your load-order!

            You should vote for http://community.qlik.com/ideas/1391

             

            Regards

            Sven

            • Re: Using a calculated value in below() to create a different size sliding window in a chart
              Lyndon Nelson

              I finally found a work around to this issue - not the most elegant solution but it works.

               

              I created a working table with a key of the three dimensions composited together. Then I would loop through the chart, getting the calculated values for OnTRR and insert that into the working table via dynamic update. I then use the field in the working table in place of the OnTRR, and IT WORKS.

               

              If anyone ever needs more detailed info, let me know.