topic Re: Need To Include A Particular Item and It's Associated Data in QlikView

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

pnn44794 — Sun, 13 Oct 2019 00:58:23 GMT

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.

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

sunny_talwar — Mon, 23 Oct 2017 16:55:57 GMT

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

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

sunny_talwar — Mon, 23 Oct 2017 16:56:42 GMT

Or this

RangeSum(

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

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

pnn44794 — Mon, 23 Oct 2017 17:11:18 GMT

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.

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

sunny_talwar — Mon, 23 Oct 2017 17:14:20 GMT

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?

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

pnn44794 — Sun, 13 Oct 2019 00:59:11 GMT

Check column F. I highlighted a few in yellow.

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

sunny_talwar — Mon, 23 Oct 2017 17:42:44 GMT

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

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

pnn44794 — Mon, 23 Oct 2017 21:48:42 GMT

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.

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

sunny_talwar — Tue, 24 Oct 2017 00:16:54 GMT

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

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

pnn44794 — Sun, 13 Oct 2019 01:00:01 GMT

Hello Again Sunny:

That suppresses all values.