I want to be able to calculate an average cost per month in a QV table, but I only want to use ten randomly selected records per month. However, I cannot presample the data before loading into QV; I need all the data loaded into the report (thousands of records per month).
I've got a file in the following format:
Scheme Month Ref Cost Random Number
A Jan-09 C1 50,000 .6413
B Jan-09 C2 1,200 .2212
B Feb-09 C3 32,300 .3254
. . . .
etc.
Initially I was going to create a column with a 0 or 1, and randomly put ten 1s against each month. However the scheme field needs to be included in a listbox to filter the report. If the user selects a scheme then there will be less than ten records per month, and I always want ten no matter what selections are made.
Instead, I created the random number column so I could pick the ten records with the lowest (random) value. This will always give you 10 records regardless of any selections.
Does anyone know how I can do this in a QV table? Do I need the random value?
Hope this makes sense! Any help would be appreciated!
Are you generating the random number in your load or within an expression using Rand()?
If you can get it as part of your load, you should be able to use Rank() on the random number field. Or you could use something like this as Set Analysis:
{1<RANDOMNUM = {'<=$(=Min(RANDOMNUM, 10))'}>}
I added the 1 in there to ignore selections, but I didn't really follow that part about the scheme. You may aso need a {1} before RANDOMNUM in the Min function.
I'm creating the random number in the file before loading the data in. I was talking about the "scheme" field to try and illustrate the problem with selections, but I don't think I explained it too well; let me try again!
I want my table to show the average of ten randomly selected claims for each month within any selections that are made. Suppose I had a male/female field in my file. When no selections are made in the application (both male and females are shown) each month will be made up of ten randomly selected males and females. If you then select males I still want ten claims selected each month, but this time they must all be males, likewise for females.
Okay, it sounds like you need to respect the selections, so get rid of that one at the beginning. QlikView will handle your selections, so if you select Male or Female, that will filter the data for you.
Where RandomNum is the name of your Random Number field. And you may only need a nine in the Min function to get the top ten, because 0 is probably the lowest.
Then if you were to select Males, the entire expression would then apply only to that selection, meaning you would get the 10 lowest random males and the average cost of those. Give that a try in your app and see what you get.
The formula you've given me selects the ten records with the lowest random number. So in the entire table there are only ten records.
The table I wish to create, however, is summarised by month and I need ten records per month. So in each month their needs to be ten records selected that have a random number lower than the 10th lowest value in that month.
