NthLargest

I have a report set with a parameter - Select # of WOs to display - 1, 2, 3, etc through 10.
 Issue  when you select 5  and there are only 4 WOs  no records return?
Thank you.

if shared stringvar rowsuppress_sv = '1'
  then {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
  else if shared stringvar rowsuppress_sv = '2'
         then {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
         else if shared stringvar rowsuppress_sv = '3'
                then {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})

Open in new window

washvtAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mlmccConnect With a Mentor Commented:
Just saw the issue

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count({WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
        else if shared stringvar rowsuppress_sv = '3'then
        (if (Count({WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count({WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count({WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE)
                    else 1=0

mlmcc
0
 
mlmccCommented:
Try this

if shared stringvar rowsuppress_sv = '1'then
    {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
else if shared stringvar rowsuppress_sv = '2' then
(    
     if (Count(,{WORKORDER.REPORTDATE}) >= 2 then
         {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
     else
         {WORKORDER.REPORTDATE} <= nthlargest(Count(,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE})
)
else if shared stringvar rowsuppress_sv = '3'    then
(    
     if (Count(,{WORKORDER.REPORTDATE}) >= 3 then
         {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
     else
         {WORKORDER.REPORTDATE} <= nthlargest(Count(,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE})
)

Similarly for each N

mlmcc
0
 
washvtAuthor Commented:
Tried this but no success - please take a look at my formula - Thank you.
Questions
For this line do I put in the number 2, 3 etc?  -     (Count(2,{WORKORDER.REPORTDATE}),    
{WORKORDER.REPORTDATE} <= nthlargest(Count(,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE})

When I check validation of the report I get-  ) is missing -  after >= 2 then - then is hightlighted
)

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count(2,{WORKORDER.REPORTDATE}) >= 2 then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(Count(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3' then
        (if (Count(,{WORKORDER.REPORTDATE}) >= 3 then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(Count(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count(4,{WORKORDER.REPORTDATE}) >= 4 then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(Count(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count(5,{WORKORDER.REPORTDATE}) >= 5 then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(Count(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
Since you have a ( after IF you need one before THEN

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count(2,{WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(Count(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3' then
        (if (Count(,{WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(Count(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count(4,{WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(Count(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count(5,{WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(Count(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0

mlmcc
0
 
washvtAuthor Commented:
Made change - now getting error message - A Field is required here - hightlighting the 2 - in (Count(2,{

 (if (Count(2,{WORKORDER.REPORTDATE}) >= 2) then

Thank you.

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count(2,{WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(Count(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3'then
        (if (Count(3,{WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(Count(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count(4,{WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(Count(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count(5,{WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(Count(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0
0
 
mlmccCommented:
COunt requires the field.  Delete the 2, and 3, etc

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count({WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(Count(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3'then
        (if (Count({WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(Count(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count({WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(Count(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count({WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(Count(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0

mlmcc
0
 
washvtAuthor Commented:
Thanks for your assistance.  

Now getting error message - A Field is required here - hightlighting the 2 - in (Count(2,{
At the <= nthlargest (Count(2,{WORKORDER.REPORTDATE})

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count({WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(Count(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3'then
        (if (Count({WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(Count(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count({WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(Count(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count({WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(Count(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0
0
 
mlmccCommented:
Since COUNT produces a number you can't use NthLargest on it.

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count({WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
        else if shared stringvar rowsuppress_sv = '3'then
        (if (Count({WORKORDER.REPORTDATE}) >= 3) then
        {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE})
        else {WORKORDER.REPORTDATE} <= nthlargest(3,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
            else if shared stringvar rowsuppress_sv = '4' then  
            (if (Count({WORKORDER.REPORTDATE}) >= 4) then
            {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE})
            else {WORKORDER.REPORTDATE} <= nthlargest(4,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                else if shared stringvar rowsuppress_sv = '5' then  
               (if (Count({WORKORDER.REPORTDATE}) >= 5) then
                {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE})
                else {WORKORDER.REPORTDATE} <= nthlargest(5,{WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE}))
                    else 1=0

mlmcc
0
 
washvtAuthor Commented:
Since COUNT produces a number you can't use NthLargest on it.

Could you please demonstrate where to take the "NthLargest" out of the formula.  Thank you.

if shared stringvar rowsuppress_sv = '1'then
   {WORKORDER.REPORTDATE} <= nthlargest(1,{WORKORDER.REPORTDATE})
    else if shared stringvar rowsuppress_sv = '2'then
    (if (Count({WORKORDER.REPORTDATE}) >= 2) then
    {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE})
    else {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE}),   {WORKORDER.REPORTDATE}))
0
 
mlmccCommented:
That should be working

mlmcc
0
 
washvtAuthor Commented:
When I check the formula - I'm getting "The ) is missing - at the comma between - Thank you.

nthlargest(2,{WORKORDER.REPORTDATE}),   {WORKORDER.REPORTDATE}))

else {WORKORDER.REPORTDATE} <= nthlargest(2,{WORKORDER.REPORTDATE}),   {WORKORDER.REPORTDATE}))
0
 
washvtAuthor Commented:
Thank you!  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.