15 Replies Latest reply: Aug 21, 2017 12:10 PM by Sunny Talwar

# More than and less than in a Filter

Hi,

I have been trying to put together a set analysis in a filter and got it to work on the basic level. i.e.

=[Shirt Orders]<=720  or

=[Shirt Orders]>1000

and the output in the filter drop down is -1 and 0 that is fine.

When I add more than or less than the functionality did not work.

=[Shirt Orders]>1000<=1200

I also tried

Count({<[Shirt Orders] = {">\$(1000)<=\$(1200)"}>} [Shirt Orders])

but it did not work.

Any help is appreciated.

Kurleigh

• ###### Re: More than and less than in a Filter

Try this

Count({<[Shirt Orders] = {">1000<=1200"}>} [Shirt Orders])

• ###### Re: More than and less than in a Filter

both of us replied in same time

• ###### Re: More than and less than in a Filter

Hi Sunny,

I forgot to add this is in a dimension.

I tried your solution but I am getting and 'Invalid Dimension" in my filter

• ###### Re: More than and less than in a Filter

Hi,

Try like this calculated dimension,

aggr(Count({\$<[Shirt Orders] = {">1000<=1200"}>} [Shirt Orders]),[Shirt Orders])

• ###### Re: More than and less than in a Filter

Hi,

Try like this

Count({\$<[Shirt Orders] = {">1000<=1200"}>} [Shirt Orders])

• ###### Re: More than and less than in a Filter

Hi Devarasu,

Thanks for you help.

The aggregate is not giving me an invalid dimension but it is not exatcly what I am looking for. When I select the filter I should see and option that allows me to only show the Shirt Orders >1000 <=1200 or remove them.

e.g.

In one of my filters I have a set analysis of =[Shirt Orders]<=720

when I select the filter in should see -1 and 0.

-1 shows me all shirt orders between 1000 and 1200

0 removes all the shirts between 1000 and 1200

Hope this helps

• ###### Re: More than and less than in a Filter

Hi,

what is expected output screen shot? tks

• ###### Re: More than and less than in a Filter

Are you trying to create Buckets

• ###### Re: More than and less than in a Filter

Hi Sunny,

I was trying to do that but because I did not know what it was called and tried to find a way around it. If I use buckets and say:

if(count({\$<[Shirts]={"blue"},[shirt orders]={"<=700"}>}[shirt orders], 'Blue Shirt orders Less than 700', if(count({\$<[Shirts]={"red"},[shirt orders]={">200"}>}[shirt orders], 'Red shirt order above 200','All other Shirts'))

Is this correct?

• ###### Re: More than and less than in a Filter

You would need to use Aggr() function.... May be like this

Aggr(

If(Count({\$<[Shirts]={"blue"},[shirt orders]={"<=700"}>}[shirt orders]), 'Blue Shirt orders Less than 700',

If(Count({\$<[Shirts]={"red"},[shirt orders]={">200"}>}[shirt orders]), 'Red shirt order above 200', 'All other Shirts')), Shirts)

• ###### Re: More than and less than in a Filter

Aggr(

If(Count({\$<[Shirts]={"blue"},[shirt orders]={"<=700"}>}[shirt orders]), 'Blue Shirt orders Less than 700',

If(Count({\$<[Shirts]={"blue"},[shirt orders]={">700<=1440"}>}[shirt orders]), 'between 700 and 1440', 'All other Shirts')), Shirts)

Even though I said only count those <=700 it still counted all the shirts in the first bucket and the second and third was not created.

• ###### Re: More than and less than in a Filter

Aggr(

If([Shirts] = 'blue' and [shirt orders] <= 700, 'Blue Shirt orders Less than 700',

If([Shirts] = 'blue' and [shirt orders] > 700 and [shirt orders] <= 1440, 'between 700 and 1440', 'All other Shirts')), Shirts)

• ###### Re: More than and less than in a Filter

Or just this

If([Shirts] = 'blue' and [shirt orders] <= 700, 'Blue Shirt orders Less than 700',

If([Shirts] = 'blue' and [shirt orders] > 700 and [shirt orders] <= 1440, 'between 700 and 1440', 'All other Shirts'))

• ###### Re: More than and less than in a Filter

Woot Woot.

Thanks man it worked!!

• ###### Re: More than and less than in a Filter

Awesome