Set Analysis - Less Than Value

[Anonymous]

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

[#2]

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

[#3]

Try indirect set analysis like this

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

[jaimeaguilar]

Could you please upload a sample of your data?

[#5]

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])

[Anonymous]

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.    

[jaimeaguilar]

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

[#8 · Accepted solution]

Can you please try below exp:

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

thanks,

Angad

[Anonymous]

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