How to retrieve Rows with “n” Greater or Less Dimension Values – Demystifying the Any and All Operators
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