I have built a cube; and am not able to get the right results from it. Here is the issue I am facing.
I have a table in the DSV where I added a named calculated column. This column depends on two other columns: Customer ID and date. This calculated column must show the minimum date with respect to the customer. Here is the query I am using:
select customerID, min(date) mindate from tableA where group by customerID
How do I build the query for the named calculation to get the minimum date of the customer?
customerID mindate
656309 6/7/2013
10348 5/17/2013
687736 1/4/2013
130943 10/12/2012
657537 10/19/2012
428661 3/8/2013
9120 5/10/2013
5250 4/5/2013
681012 10/5/2012
37169 1/18/2013
You could try the following expression
min(date) over(partition by customerID)
This may or may not work in a calculated column of the DSV. If it does not work as a calculated column, you could use this expression in a named query instead. To do this, right click on the table in the DSV, and select Replace table/with named query. This shows up a SQL select statement. In this, you can add the above expression, and it should work in any case.