Sorting expression

[Anonymous]

I have data that needs to be sorted into specific groups. The data is going to updated each night so can't add anything into the LOAD script.

The data is in the following format

File            UniqueRef          Amount

ABC          123abc                   4.6

BEE           112dfd                    12    

DEE            443asd               -0.52

What I have been trying to do is sort them into groups where Amount < 0 would be put into that group/column

amount > 12 into another group

Amount <5 etc.

So far in the dimension i have the following which WORKS. - But i can't replicate into the expression.

=if ([Amount] <5, '<5%',

if ([Amount] <=7  , '6-7%',

if ([Amount] >10, '>10%',

)))))) 

With Count(unique ref)as expression

How can i put everything in the expression - Leaving just file name as the dimension - I have tried in the expression

count(([UniqueRef]) and [amount] <5)

but it doesn't work.

Thanks

[Anonymous]

try this

count({<$[amount]={'(='<5')'}>}[UniqueRef])

[Anil_Babu_Samineni]

One solution may be

=if ([Amount] <5, Dual('<5%', 1), if ([Amount] <=7  , Dual('6-7%',2), if ([Amount] >10, Dual('>10%',3)))) as FieldName

And expression is

count({<FieldName = {'<5%'}>} UniqueRef)

[Anonymous]

That formula is pretty much the same one I tried in my expression too - Issue is the numbers are wrong that it produces.

[Anil_Babu_Samineni]

May be negative values also considering as <5% in your expression that you don't want?