How to write a query to filter data between two dates.i.e


Suppose i want to find the sales of a person from 2nd march 2004 to
3rd october 2004.

i.e in short what can i write in mdx in place of 'between' in SQL.

what are you trying to do; can you give more examples?


"Amit Asawa" <amit.asawa (AT) capgemini (DOT) com> wrote

I'm looking for something similar...

In As you normally don't choose from-date - you choose date as part of a
tupple (think it's called) and you'll see the results
in the cube corresponding to what you've chosen.

However you can use MDX date functions such as ytd where you get Year to
date but that doesn't sound like your need.

Here's some threads that I'll be looking at to see if it helps...

http://groups.google.com/groups?q=ol...elm=e2JJFl7 3
DHA.1096%40TK2MSFTNGP11.phx.gbl&rnum=4
http://groups.google.com/groups?hl=e...72%40TK2MSF T
NGP09&rnum=11&prev=/groups%3Fq%3Dolap%2Bdate%2Binterval%26start%3D10%2 6hl%3D
en%26lr%3D%26selm%3DuPKpmzczCHA.1672%2540TK2MSFTNG P09%26rnum%3D11

Another way to go would be to lock it a little more (not se flexible but
then you wouldn't need to code so much in As and you won't get drillthrough
trouble with calculated members) .... you could in your time dimension table
put in logic that marks current day,
current month, current quarter and so on with a parameter or "last thirten
weeks".

That would of course mean that this flagged needed to be set as part of your
ETL process but that shouldn't be too hard.....

I would check out if this could solve the problem if it was my problem to
solve...


"Amit Asawa" <amit.asawa (AT) capgemini (DOT) com> wrote

Here was an intesting input...

Twin timestamps....

Haven't read it all yet....

http://www.intelligententerprise.com...tml?_requestid
=311780

It was a link from this site

www.ralphkimball.com

"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

....sorry but I don't see how it can be used in AS.

My interpreation of the article is that you would choose one thing in one
dimension and another thing in the other dimension. Then I won't get
a tupple - will I ?

Anyone ?

"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

I think it can be done very easily if I understand the question. Create a
calculated member in the time dimension to represent your time span. For
instance,

WITH MEMBER [Time].[TimeSpan] AS
'Aggregate([Time].[Day].[20040302]:[Time].[Day].[20041003])'

Then use the calculated member in the WHERE clause

SELECT .... ON COLUMNS,
.... ON ROWS
FROM [Sales]
WHERE ([Time].[TimeSpan], [Measures].[Unit Sales])

The results would give you the totals for the time span specified. Is this
what you were looking for? It will give you the same results as a BETWEEN
clause in SQL.

"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

Sounds interesting - raises some trouble however

a) I cannot influence the MDX-statement from the front-end so I would have
to make you member on the server - and then
it won't be a dynamic member - it needs to be one presented to the end-user.

b) By aggregating I cannot make drill-through in the same report ? But I
could make drill through by calling an action to
a report that does drill directly to the fact table instead

So a is the worst obstacle....

Another way to go was to change the front-end to work as you describe ....
in one front-end it could be an option but not in
the other one...

"Martin Mason" <martinma (AT) mail (DOT) wt.net> wrote

The standard way I solve this particular problem is described here
http://groups.google.co.uk/groups?hl...40TK2MSFTNGP12
On a related issue, if query performance is a problem when running queries
which sum up multiple time periods you might be interested to read the
following post (and its immediate successor in the thread) too
http://groups.google.co.uk/groups?hl...0microsoft.com

It doesn't get round the drillthrough issue though - you'd need to do the
action workaround instead.

HTH,

Chris

"Michael Vardinghus" wrote:

(Don't know if this is a double posting - I got an error at my last attempt
to post an answer, so I'm trying again)

The standard way I solve this problem is described here
http://groups.google.co.uk/groups?hl...40TK2MSFTNGP12

On a related note, if you have performance problems in your queries when
you're summing up multiple time periods, you might be interested to read this
post and its immediate successor in the thread
http://groups.google.co.uk/groups?hl...0microsoft.com

Of course, this approach doesn't get round the drillthrough problem - as you
said, you'd need to create an action to do it instead.

HTH,

Chris
"Michael Vardinghus" wrote:

Wouv....that was solved by using a special approach.....doing something
outside of
AS to AS - wouldn't have thought of that...that's for sure


"Chris Webb" <OnlyForPostingToNewsgroups (AT) hotmail (DOT) com> wrote

%40TK2MSFTNGP12
A485B281B%40microsoft.com