Need To Include A Particular Item and It's Associated Data

[pnn44794]

Hello:

I have a need to include a particular DM # along with the Top 10 DM #'s by specific Foundation. 

As always, thanks in advance for any and all help / replies.

[sunny_talwar]

May be this

RangeSum(

If(Num(Rank(TOTAL Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty))) < 11,

Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty)),

Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty))

[sunny_talwar · Accepted solution]

Or this

RangeSum(

If(Num(Rank(TOTAL Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty))) < 11,

Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty)),

Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #] *= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty))

[pnn44794]

Hello Sunny:

Thanks for the quick reply.  So, I tried the second one and it does indeed appear to work.  A big thank you.  So simply adding an additional Sum with that DM # = 88888888888-11, did the trick.  I noticed though that if the DM # is not the 8888 DM #, it puts a zero in the field.  Is there a way to suppress the zero?  I did check under the Presentation tab and Suppress Zero-Values is indeed checked.

As an FYI, I will be away for about an hour and a half and will respond when I get back.

[sunny_talwar]

I noticed though that if the DM # is not the 8888 DM #, it puts a zero in the field.

I am not sure I understand this.... Would you be able to show an image of this?

[pnn44794]

Check column F.  I highlighted a few in yellow.

[sunny_talwar]

Although the cell highlighted in yellow are 0, but there are other expressions which have values... that is why the line stays. If you want to change them to null instead... try this

RangeSum(

If(Num(Rank(TOTAL Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty))) < 11,

Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty)),

If(Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #] *= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty) > 0,

Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #] *= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty)))

[pnn44794]

Hello Sunny:

Sorry for the delay.  I tried the above, but still get 0 in the column.  I understand why and thanks for explaining.  I'd still like to suppress the 0 if possible.

[sunny_talwar]

What do you mean when you say suppress the 0? You want to remove the row? But as long as one of the expressions have non zero values, you won't really be able to suppress it. For example, row number 1 has a value for VSI-D, and that is why you see a 0.

0 might be coming because of RangeSum... may be try this

If(Num(Rank(TOTAL Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty))) < 11,

Sum({<project_include_in_forecast = {1},project_is_active = {1},
estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #]-= {'88888*'}, [Item Action]={'Add'} ,
Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}
>} effective_item_qty))

+

If(Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #] *= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty) > 0,

Sum({<project_include_in_forecast = {1},project_is_active = {1},

estimate_include_in_forecast={1},estimate_is_active ={1}, [DM #] *= {'88888888888-11'}, [Item Action]={'Add'} ,

Foundation *= {'PSI Basic', 'PSI-Basic-HDD'}

>} effective_item_qty))

[pnn44794]

Hello Again Sunny:

That suppresses all values.