topic Re: Set Analysis - Less Than Value in QlikView

Set Analysis - Less Than Value

Anonymous — Mon, 24 Mar 2014 22:19:18 GMT

Hi,

I'm trying to create a total in a text box for items that need reordering, I have tried the following and constantly get a total of 0:

count({$<[On Hand Qty]={"<[Reorder Point]"} >} [2nd Item Number])

Is what I'm try to-do possible, alternatively I already have a chart that shows the records, is there anyway to count the rows in a chart and display this in a text object?

Thanks

Kevin

Re: Set Analysis - Less Than Value

Mon, 24 Mar 2014 23:15:15 GMT

I don't know about your data, but the set analysis expression seems to be wrong

Somehow , you have to include values instead of the field

For instance

count({$<[On Hand Qty]={10,20} >} [2nd Item Number])

if the values is bringing across from other field you could use "concat" function for example

i hope this clarifies a bit

Re: Set Analysis - Less Than Value

Mon, 24 Mar 2014 23:20:04 GMT

Try indirect set analysis like this

count({$<[On Hand Qty]=p([On Hand Qty]={"<[Reorder Point]"}[On Hand Qty])>}[2nd Item Number])

Re: Set Analysis - Less Than Value

jaimeaguilar — Tue, 25 Mar 2014 00:16:23 GMT

Could you please upload a sample of your data?

Re: Set Analysis - Less Than Value

Tue, 25 Mar 2014 05:29:22 GMT

At first place, can you share a sample app. But you can try as follows:

>> count({$<[Reorder Point]={">=[On Hand Qty]"} >} [2nd Item Number])

>> count({$<[On Hand Qty]={"<=[Reorder Point]"} >} [2nd Item Number])

Re: Set Analysis - Less Than Value

Anonymous — Tue, 25 Mar 2014 13:00:02 GMT

Unfortunately none of the suggestions seem to work, also I'm unable to upload the app as it contains client data. I can answer any questions regarding the app though so please let me know if I need to provide any further clarification.

Re: Set Analysis - Less Than Value

jaimeaguilar — Wed, 26 Mar 2014 16:33:44 GMT

Hi, I think this is not possible with set analysis, but you can use an expression like this:

=count(distinct if([On Hand Qty]<[Reorder Point], [2nd Item Number]))

try that expression in a text box,

regards

Re: Set Analysis - Less Than Value

Thu, 27 Mar 2014 06:17:47 GMT

Can you please try below exp:

=count( {$<[2nd Item Number] = {"=[On Hand Qty]<[Reorder Point]"}>} DISTINCT [2nd Item Number])

thanks,

Angad

Re: Set Analysis - Less Than Value

Anonymous — Thu, 27 Mar 2014 18:03:40 GMT

Hi Angad,

I had previously tried your suggested expression but had questioned the results, it looks like it was working after all but there are some inherent problems with the data itself. Since clarifying the correct data structure the expression is now working.

Thanks

Kevin