1

Unpredictable behavior with rand function

Kmaloney

February 20, 2019 17:22

Answered

Follow

I am attempting to create a report that will pull inventory items, with qty on hand, that match the cycle count code we wish to check. Those items are data dumped on one sheet, and then I use the a combination of INDEX and RAND to select 5 random item numbers. Those numbers are pulled to the main report page and used in the NL query to target the specific 5 random items. Both running the report dynamically (either listing the array of item numbers or building the filter string) and I have tried statically (nl first command for each of the 5 selected). That static report works but only if I switch between design mode to report mode. Subsequent "refreshes" break report and return nothing. The dynamic report works on subsequent "refreshes" but doesn't always return the correct information as if the item no it is supposed to be looking at changes mid query.

Dataset with random selection image
random formula  =INDEX(D12:D13,RANDBETWEEN(12,ROWS($D$12:$D$13)),1)

and it works going from design to report mode

Try to just refresh the report though and

new set of numbers but no data.

My assumption is that the order of operations for how excel handles its functions and the way the jet formula functions are both evaluated is causing the issue.

Has anyone encountered an issue like this before?

• Facebook
• Twitter
• LinkedIn

11 comments

• 0
  
  Harry Lewis February 20, 2019 18:04

  Hello -

  I'm trying to duplicate the scenario so that I can better understand what you are trying to do.

  Unfortunately, I'm not there yet.

  Here's what I put together:

  

   

  As you can see, I have a hard-coded list (an "array") of item numbers.

  My thought is that I want to select 5 random items from that list.

  To accomplish that, I use Excel's RANDBETWEEN function to generate a number between 1 and the total number of rows in my array that extends between D12 and D31. 

  Since RANDBETWEEN is a volatile function (which Excel will re-evaluate after each and every change to the sheet), I used the NP(Eval) function to ensure that each of the 5 random values is calculated only once.

  I also separated this out into its own cell, so that I can verify which row it is supposed to be pulling from.

  I then used Excel's INDEX function to get the number contained in that random row of the array.

  This gives me my 5 random item numbers.

  I then use an NL(Rows) function to list those 5 numbers, and simply queried the Description from my database for each of the numbers.

  

   

  Obviously, this is different from what you are doing.

  Can you explain your process to me so that I can better evaluate?

  Edited by Harry Lewis February 20, 2019 18:10
• 0
  
  Kmaloney February 20, 2019 19:07

  Absolutely mate and thank you so much for the immediate response!
  
  The major difference between your file and mine is the hardcoded dataset.
  
  Mine is a generated datadump using user option criteria and a simple nl dump function.
  
  
  =NL("Rows","Item",,"Phys Invt Counting Period Code",$C$3,"Quantity on Hand",">0","Last Phys. Invt. Date",$C$8)
  

  I then do the same RANDBETWEEN and INDEX selections that you do, however because I have a variable row set, I do not eval the result till the next sheet when I pull those values into the main report sheet.
  

  Then I have actually tried doing the item list with a constructed filter string (dynamic) and a hardcoded filter (static) to each of the five.

  static
  dynamic
  there is the filter string build, then 
  
  
  Now the dynamic one produces odd results in that sometimes the item no. is not the item no. that matches the displayed description, which is odd as the np eval should prevent the updating of the search no.

  The static one will work if going from design mode to report mode, but will not work if just refreshing report mode.  Furthermore if you unhide a the blank refreshed report then all the data populates as expected.

• 0
  
  Harry Lewis February 20, 2019 23:22

  I believe that you are experiencing is due to the volatility of RANDBETWEEN functions.

  For example, I modified my report to dynamically generate the list of Item No. rather than have them static.

  This also meant that I had to remove the NP(Eval) function (otherwise, there would be not way for the value representing the number of rows to expand dynamically).

  Because the RAND... functions recalculate with each (even minor) change to the sheet, the values will refresh multiple times as the report attempts to complete.  Thus, another cell can get one of those random value, but when it gets the data corresponding with that value, that value then changes.

• 0
  
  Kmaloney February 21, 2019 13:29

  I still think this is unexpected behavior overall as those volatile functions can be “reigned” in somewhat from their automatic calculation by flipping that setting on the “Formulas” ribbon under Calculation Options.

  

  By setting that to Manual then the rand or randbetween functions "should" only run once. 

• 0
  
  Harry Lewis February 21, 2019 15:21

  Interesting point.  

  I know of no way to force Excel to do that with the volatile functions.

  Maybe someone else will have ideas.

• 0
  
  Kmaloney February 21, 2019 20:05

  So I have been able to further identify the issue and workaround for it.  Still unreliable enough behavior for what I am trying to achieve. 

  However it does prove my point that there is something that makes excel functions run each time that jet moves from sheet to sheet in the workbook.  I was able to prove this by setting the calculation type to manual, then moving the report results to below the data pull.  

  Design mode:

  

   

  After report run:
  

   

  Still unsuccessful expanding the random selection to exclude duplicates. 
  
  

• 0
  
  Kmaloney February 21, 2019 20:17

  Jet Reports Feature Request:

  Be able to randomly select a grouping of records from a filter set.  Would be an amazing expansion to the function set to be able to do =NL("Random=NumberOfRandoms",other,filter,criteria)
  
  Especially since excel's random functions do not play all that well with jet.

  Edited by Kmaloney February 21, 2019 20:18
• 0
  
  Harry Lewis February 21, 2019 21:31

  Thanks -

  If you feel that such a feature would be beneficial to the Jet community, as a whole, I would encourage you to submit a feature request on the Jet Ideas Portal (accessible via the Feedback link to the right).

  

• 0
  
  Kmaloney February 22, 2019 14:33

  Thank you Harry! I have done just that.  If you think it would be beneficial for the community as well then feel free to toss it a vote. :)
  
  https://jetideas.ideas.aha.io/ideas/JETREP-I-365 

• 0
  
  Harry Lewis February 22, 2019 14:47

  I got together with one of our most experienced folks (now a VP) and here is a method that would supply a majority of what you are looking to do.

  In this example, we first have an NL(Rows) function that lists some some records:

  

   

  Then, so that we can now exactly how many records there are, we duplicate that function as an NL(Count):

  

   

  Then, rather than selecting random information from a row in the report, we select random records directly from the database:

  

   

  When I run the report, it would look like this:

  

• 0
  
  Kmaloney February 22, 2019 15:33

  WOW!  The Jet support team just continues to amaze me.  Going WAY WAY above and beyond!  I appreciate both you and that extremely talented VP taking your personal time out and digging into this with me.  I have implemented their suggestion and I can reproduce the results consistently.

   

  I certainly think that feature request would seriously simplify this matter, but nice to see it still can be pulled off.  Embedding a eval in the "what" section is a new one for me.  Never knew I could bypass the "what" parameter and just send a record number directly.  Definitely interesting and I will have to mull over new and creative ways I can use this trick. 
  
  Thank you both again for the absolutely amazing support!  

  Three cheers for JET!!! 

  HIP HIP HOORAY!

  HIP HIP HOORAY!

  HIP HIP HOORAY!