Find min value of member, MDX

Question

Just started to explore MDX. Can anyone help me to get below result.

Looking to build a MDX query which gives same out as below SQL query

select max(date),min(date) from Fiscal_calendar

Where : 1. Fiscal_Calendar is dimension in cube 2. date is the attribute

SouravA · Answer 1 · 2016-01-09T08:15:06.200

3

Min Date will be the First Child of the [All] member while Max Date will be the Last Child.

SELECT {Fiscal_Calendar.Date.[All].FirstChild, Fiscal_Calendar.Date.[All].LastChild} ON 1,
{} ON 0
FROM [YourCube]

Second last child:

Fiscal_Calendar.Date.[All].LastChild.Lag(1)

edited Jan 09 '16 at 08:15

answered Jan 09 '16 at 06:51

SouravA

5,147
2
24
49

Thanks SouravA! now I'm able to see firstchild value but I'm getting blank for lastchild. Can you guide me to fetch lastchild value which is not blank? – Kuntady Yathish Jan 09 '16 at 07:18
Is your LastChild always blank? How is the second last child? If it's certain to be not blank, can you fetch the second last child always? – SouravA Jan 09 '16 at 07:25
Yes. Last child was blank. Your snippet worked out for me.. Bunch of thanks! – Kuntady Yathish Jan 09 '16 at 08:50
@reachyathish what does Sourav's second snippet return? – whytheq Jan 10 '16 at 12:44

score 0 · Answer 2 · answered Jan 09 '16 at 08:07

0

SELECT {
        [Dimension - Fiscal Calendar].[Fiscal Quarter].members.item([Dimension - Fiscal Calendar].[Fiscal Quarter].members.count-2)
        ,[Dimension - Fiscal Calendar].[Fiscal Quarter].[All].firstChild
    } ON 1,{} ON 0 FROM [MyCube]

Thanks @SourabA. Above code worked out to me. Do let me know if any better approach

answered Jan 09 '16 at 08:07

Kuntady Yathish

49
2
10

Your answer is not consistent with the information you supplied. Also, you are basically doing the same thing as the answer I provided, in a slightly more complicated way. – SouravA Jan 09 '16 at 08:18
Might be.. @sourabA could you suggest a better code item to get secondlastchild.. I could not find a MDX keyword – Kuntady Yathish Jan 09 '16 at 08:25
There is no better approach unless you are open to making changes to the cube structure. I added something for the second last child in my answer which at best is an alternative. – SouravA Jan 09 '16 at 08:32
@reachyathish - you have reinvented Lag in your code! - stick to Sourav's suggestion. Why is the last member empty? This suggests a problem in your database. – whytheq Jan 10 '16 at 12:46

Find min value of member, MDX

2 Answers2

Linked