topic Re: Nested IF statement List Box in QlikView

Nested IF statement List Box

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have situation where I've a if statement which isn't filtering out the right way. It shows Small when it should be showing Middle and Middle when it should be showing Large.

<= 500 Small

<=12000 Middle

else Large

Please provide insights!!!!!!!!!

Thanks,

Re: Nested IF statement List Box

engishfaque — Wed, 06 Apr 2016 15:01:15 GMT

Dear Arsalan,

Try this one expression,

=If(YourFieldName > 0 and YourFieldName <= 500, 'Small', If(YourFieldName > 500 and YourFieldName <= 12000, 'Middle', 'Large'))

Please note, you can perform aggregate function on your YourFieldName.

Kind regards,

Ishfaque Ahmed

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 15:06:39 GMT

May be try this..

=Aggr(Only({1}If(RangeAvg(SepRound, OctRound, NovRound, DecRound, JanRound, FebRound) <= 500, 'Small',

If(RangeAvg(SepRound, OctRound, NovRound, DecRound, JanRound, FebRound) <= 12000, 'Middle', 'Large'))),[Internal ID],Customer)

Re: Nested IF statement List Box

Wed, 06 Apr 2016 15:08:49 GMT

Thanks Ishfaque you're right I think there's nothing wrong with my list box formula the formula for the categories is wrong.

It shows 132.17 as Middle even though it should be Small. I'm currently using

=If(RangeAvg(SepRound, OctRound, NovRound, DecRound, JanRound, FebRound) <= 500, 'Small',

If(RangeAvg(SepRound, OctRound, NovRound, DecRound, JanRound, FebRound) <= 12000, 'Middle', 'Large'))

Re: Nested IF statement List Box

Wed, 06 Apr 2016 15:08:49 GMT

!RangeAvg is not an aggregation function. So it may be give wrong results.

Aggr(Only({1}If((SepRound+OctRound+NovRound+DecRound+JanRound+FebRound)/6 <= 500, 'Small',

If((SepRound+OctRound+NovRound+DecRound+JanRound+FebRound)/6 <= 12000, 'Middle', 'Large'))),[Internal ID],Customer)

Please find the attached qvw file.

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 15:10:36 GMT

Change your Category expression in Table as below

IF(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)) <= 500, 'Small',

IF(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)) <= 12000, 'Middle','Large'))

Re: Nested IF statement List Box

Wed, 06 Apr 2016 15:12:07 GMT

My list box formula is the right formula to be used it just filters out the Small, Middle and Large. the problem is the Categories formula in the table.

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 15:16:22 GMT

=IF(Aggr(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)),[Internal ID],Customer)<=500,'Small',

IF(Aggr(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)),[Internal ID],Customer)<=12000,'Medium','Large'))

Re: Nested IF statement List Box

marcus_sommer — Wed, 06 Apr 2016 15:16:54 GMT

The reason is that your listbox-expression is built with a range-function directly on the fields and your chart-average expression is different from this and the range-function ignored the NULL. You could change one of them but I suggest before you adapt these listbox/expression to change your datamodel and to transform your table with The Crosstable Load and to use a master-calendar to link your period-field: How to use - Master-Calendar and Date-Values.

- Marcus

Re: Nested IF statement List Box

Wed, 06 Apr 2016 15:36:57 GMT

I can't seem to figure this out. Can anyone provide assistance.

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 15:42:45 GMT

Try this may be:

=If(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)) <= 500, 'Small',

If(RangeAvg(Sum(SepRound),Sum(OctRound),Sum(NovRound),Sum(DecRound),Sum(JanRound),Sum(FebRound)) <= 12000, 'Middle', 'Large'))

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 15:47:34 GMT

Your application attached with the correct expression for the Categories 661 list box also

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 15:48:58 GMT

may be try this..

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 15:52:56 GMT

Middle may be 661 and not 668. May be I could be wrong..

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 15:54:14 GMT

Seems like the number keeps on getting changed everytime

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 15:57:13 GMT

Got it, it seems to be driven by an expression I get 661 still. Is 661 for Middle incorrect?

Re: Nested IF statement List Box

MK_QSL — Wed, 06 Apr 2016 16:06:22 GMT

661 should be correct as per my view. Have you consider Customer as well in listbox aggr?

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 16:07:56 GMT

I did not, but I think I should because that is one of the dimensions on the chart, right?

Re: Nested IF statement List Box

sunny_talwar — Wed, 06 Apr 2016 16:08:26 GMT

I guess if Internal ID is unique, we might not need customer.

Re: Nested IF statement List Box

Wed, 06 Apr 2016 16:11:19 GMT

Thanks for all your help guys, remarkable teamwork!!!!!