Number Ranges

[rchiew604]

Trying to extract below number ranges:

- Amount larger than 1000

AND

- Amount smaller than -1000

Below is my expression:

num(sum({<[Ledger Type]={'AA'},Source={'F0911'},Amount={">1000<-1000"}>} Amount/100),'#,##0')

However it is still pulling in zeros and numbers smaller than 1000 and larger than 1000.

Can anyone help on how to fix it?

Thanks

[bwisealiahmad]

Hi,

Made a table with following inline table:

Figures:

LOAD * INLINE [

    ID, Value

    1, 1500

    2, 999

    3, -999

    4, -1500

];

And used this expression:

Sum({<Value = {">=1000<=-1000"}>}Value)

Bit similar to your expression:

num(Sum({<Value = {">=1000<=-1000"}>}Value / 100),'#,##0')

And that gave me:

Is this what you are trying to do?

Added .QVW also for you.

Best,

Ali A

[bwisealiahmad]

Also,

If you could add some data that you have could be easier to try with that data...

[rchiew604]

Thanks Ali.

Other than using the inline method, is there a way to simply use set analysis to return the data that I need?

If I use the Amount={">1000"}, it gives me correct data that larger than 1,000.

Likewise, Amount={"<-1000"} returns anything less than -1,000.

The problem is when I combine them into Amount={">10000000<-10000000"} which returns inconsistent data.

[Neymar_Jr · Accepted solution]

Hi Richard,

Try this -

Sum({<Amount= {">1000"},[Ledger Type]={'AA'},Source={'F0911'}>

+

<Amount= {"<-1000"},[Ledger Type]={'AA'},Source={'F0911'}>

}Amount)

Thanks,

RT

[rchiew604]

Thanks Rajesh that works.