topic Re: how to exclude a value from list box in QlikView

how to exclude a value from list box

venkat1 — Wed, 25 Nov 2020 16:16:04 GMT

my list box expression was like this in the list box

=IF(YM.YEAR_1=2016,'2015-16',
IF(YM.YEAR_1=2017,'2016-17',
IF(YM.YEAR_1=2018,'2017-18',
IF( YM.YEAR_1=2019,'2018-19',
IF(YM.YEAR_1=2020,'2019-20',
IF(YM.YEAR_1=2021,'2020-21',
IF(YM.YEAR_1=2022,'2021-22',

)
)
)
)
)
)
)

now i dont want to the year YM.YEAR_1=2016 , how to write an expression to not show YM.YEAR_1=2016 in the list box

Re: how to exclude a value from list box

vikasmahajan — Tue, 09 Apr 2019 11:21:20 GMT

try some thing like this =IF( not match( YM.YEAR_1,'2016'),0 , IF(YM.YEAR_1=2017,'2016-17', IF(YM.YEAR_1=2018,'2017-18', IF( YM.YEAR_1=2019,'2018-19', IF(YM.YEAR_1=2020,'2019-20', IF(YM.YEAR_1=2021,'2020-21', IF(YM.YEAR_1=2022,'2021-22', ) ) ) ) ) ) )

Re: how to exclude a value from list box

sunny_talwar — Tue, 09 Apr 2019 11:27:15 GMT

How about you just remove it from the if statement

=If(YM.YEAR_1 = 2017, '2016-17',
If(YM.YEAR_1 = 2018, '2017-18',
If( YM.YEAR_1 = 2019, '2018-19',
If(YM.YEAR_1 = 2020, '2019-20',
If(YM.YEAR_1 = 2021, '2020-21',
If(YM.YEAR_1 = 2022, '2021-22',
)
)
)
)
)
)

Re: how to exclude a value from list box

venkat1 — Tue, 09 Apr 2019 11:33:41 GMT

hi it was working if write like this

=IF( not match( YM.YEAR_1,'2016'),
IF(YM.YEAR_1=2017,'2016-17',
IF(YM.YEAR_1=2018,'2017-18',
IF(YM.YEAR_1=2019,'2018-19',
IF(YM.YEAR_1=2020,'2019-20',
IF(YM.YEAR_1=2021,'2020-21',
IF(YM.YEAR_1=2022,'2021-22',

)
)
)
)
)
)
)

i have another question for suppose if i want to hide 18 and 19 years, then how i tried this way it was showing error in expression.

Re: how to exclude a value from list box

andradetobias — Tue, 09 Apr 2019 11:46:48 GMT

You can exclude this in your load.
If you want to keep this year on your load, you can use the expression bellow

if(not match(FIELDNAME,'CRITERIA'),FIELDNAME)

Re: how to exclude a value from list box

venkat1 — Tue, 09 Apr 2019 11:50:06 GMT

hi sunny

i need a help from you regarding a scenario.

i am having year and month list boxes and pivot table with sales of gold and diamond by branch wises,

my requirement is to show by default max(date) sales ,where date fields are different for the gold and diamond,eg. g.vou_date,d.vou_date, now if i select the year and month list box then sales should come accordingly.

i tried with open sheet triggers of two field events with d.vou_date and g.vou_date but it was not working.

can you please help me how to do this scenario.

Re: how to exclude a value from list box

sunny_talwar — Tue, 09 Apr 2019 11:51:49 GMT

I am not sure I understand? How is this related to excluding a value from list box? Is this a new requirement? or is this somehow linked?

Re: how to exclude a value from list box

venkat1 — Tue, 09 Apr 2019 11:52:59 GMT

this was a new requirement.

Re: how to exclude a value from list box

sunny_talwar — Tue, 09 Apr 2019 11:57:39 GMT

If this is something new, I would suggest to open a new thread for it. For this thread, did you find what you were looking for? If you did, please close this thread.

Best,
Sunny