Nested IF statement List Box

[#1]

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,

[engishfaque]

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

[MK_QSL]

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)

[#4]

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

[#5]

!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.

[MK_QSL]

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

[#7]

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.

[MK_QSL]

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

[marcus_sommer]

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

[#10]

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