Retrieve record based on table expression

richardpayne — Fri, 20 Nov 2015 18:27:31 GMT

I would like to know how to retrieve the appropriate record in the following scenario:

I have a table with an expression "Demand" which is the distinct count of a field value. Simple. The records counted are based not just on a selection, but on a variable date range specified by variables in input boxes. I give this exposition because it does not seem that my solution lies in the script.

Nevertheless, as indicated by the image attached, I would like to determine the appropriate Stock Qty value to return by finding the record whose Min/Max Threshold range contains the value I calculate in "Demand". Any pointers? I'm sure there's a function I'm failing to consider or implement properly. There has to be an easy way to do this.

demand = count({<[End Date]={">=$(vStartDate)"},[Start Date]={"<=$(vEndDate)"}>} distinct [Serial Number])

stock qty = ???

The ugly and unscalable way of doing this is:

=if(Demand>=min([Threshold Min]) and Demand<=min([Threshold Max]),

min([Stock Qty]),

if(Demand>=min([Threshold Min],2) and Demand<=min([Threshold Max],2),

min([Stock Qty],2),

if(Demand>=min([Threshold Min],3) and Demand<=min([Threshold Max],3),

min([Stock Qty],3),

if(Demand>=min([Threshold Min],4) and Demand<=min([Threshold Max],4),

min([Stock Qty],4),

if(Demand>=min([Threshold Min],5) and Demand<=min([Threshold Max],5),

min([Stock Qty],5),

0)))))

topic Retrieve record based on table expression in QlikView

Retrieve record based on table expression