How to retrieve Rows with "n" Greater or Less Dimension Values - Demystifying the Any and All Operators

[Former Member]

Hi Everyone I would like to discuss the Query Filters assignment type operators Any and All.

These options are available when you working with Result from Another Query and choose, as query filter operator greater, greater or equal, less, less or equal. I will give an example in e-fashion.

I need a first query (Q1) in which I will use the result to filter another query (Q2)

Here´s Q1 :

Which gives me the following table

I will build Q2 over the results of this query.like the following

There is a huge difference using one or another. Here´s how it works :

When using greater then or greater or equal

   ALL - returns data greater than the maximum value of Q1

   ANY - returns date greater than the minimum value of Q1

then , the table resulting from Q2 using greater than or equal Result from another Query ALL is

Year

2006

using greater  than or equal Result from another query ANY is

Year

2004

2005

2006

Using less than or less than or equal alters the behaviour to

ALL - returns data from less  than the minimum value of Q1

ANY - returns data from less than the maximum value of Q1.

the table resulting from Q2 using less than or equal result from another query ALL is

Year

2004

Using less  than or equal Result from another query ANY is

Year

2004

2005

2006

It opens up space to do interest things .

Lets suppose that, in e-fashion the values for the [Year] dimension represents all years up to the present year, so, in my example this year is 2006.

Now, what if you want to get the data form the previous Year. 2005.

Of course you always could hardcode the Year 2005 or set a prompt on Year.

The first approach gives a problem that having to manually change the filter each Year.

And the second has the burden of asking the user to enter a value.

Using the following approach, you will always get the previous year.

Use Q1 and Q2 above , and setting the Q2 filter to

Finally, create Q3 which will give you the related data to Year-1

You will always get the data from Current Year -1.

But what if you want to get Current Year -2 :

You will need to create a fourth query Q4.and modify Q3 to

Which produces the following table

Year

2004

2005

And thenmodify Q3 over the result of Q2

And finally create Q4 over the result of Q3 :

You can add more queries to the process if you wanna get deeper in the past years, just create another query (Qn) getting the data less than result from another query (Qn-1) ANY .

I call this process as "slicing" the dimension

Hope I have expressed myself clearly.

Any doubts or suggestions, contact me.

Merry Christmas to you all,

cheers,

Rogerio