Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
may be try this..
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
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)
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'))
!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.
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'))
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.
=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'))
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
I can't seem to figure this out. Can anyone provide assistance.