How to get the measure for the last day in a month in DeepSee

Question

Question

Evgeny Shvarov · Apr 5, 2017

Hi, Community!

How can I manage to show in MDX query months where every month shows the value of the last month?

Say, in HoleFoods the query:

SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HOLEFOODS]

will show me the sum of revenue per month.

This query:

SELECT NON EMPTY [Measures].[Amount Sold].MAX ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HOLEFOODS]

will show the Max revenue in a day for the given month.

But what MDX will show the months with the revenue for the last day in a month?

Discussion (11)0

Log in or sign up to continue

Alessandro Marin · Apr 5, 2017

%LAST evaluates a measure or numeric expression for the last non-empty member of a set, so I do not think that is what you want.

I am using %LASTCHILD, but you cannot append it to [DateOfSale].[Actual].[MonthSold].Members because %LASTCHILD only works on specific member. For the moment I found this solution:

WITH MEMBER [DateOfSale].[LastDayOfMonth] AS '[DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD'

SELECT [Measures].[Amount Sold] ON 0,

NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[MonthSold].Members,[DATEOFSALE].[LASTDAYOFMONTH]) ON 1 FROM [HOLEFOODS]

The resulting headers are ugly and I have not yet been able to successfully use [DateOfSale].[Actual].[MonthSold].CurrentMember.Properties("Name")

0 0

score 0 · Answer 1 · 2017-04-05T17:52:39-04:00

Eduard Lebedyuk · Apr 5, 2017

Use %Last somehow?

0 0

score 0 · Answer 2 · 2017-04-06T13:54:00-04:00

Evgeny Shvarov · Apr 6, 2017

Hi, Alessandro!

I tried that, it shows no value in my Analyzer. Version 2016.2

0 0

score 1 · Answer 3 · 2017-10-08T07:22:09-04:00

Finally, I ended out with the following expression:

WITH MEMBER [DateOfSale].[LastDayOfMonthRevenue] AS 'AGGREGATE([DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD,MEASURES.[Amount Sold ])'

So if you looking to get the value for measure other than you have in context AGGREGATE would be helpful.

This approach is very useful e.g. if you want to show daily running total but for the monthly scope.

Thanks [@Alessandro Marin] for an extra DeepSee support session on GLobal Summit 17 Tech Exchange ;)

score 0 · Answer 4 · 2017-04-06T14:14:43-04:00

The query I posted should return the revenue on the last day of a month (for example 31 Jan 2017, 28 Feb 2017, 31 March 2017). Maybe your data does not have any revenue on some last day of the month.

I probably misunderstood your questions. In particular these questions you wrote:
"How can I manage to show in MDX query months where every month shows the value of the last month?"

"But what MDX will show the months with the revenue for the last day in a month?"

Could you clarify?

Thanks

score 0 · Answer 5 · 2017-04-09T03:16:00-04:00

Hi, Alessandro!

Let me simplify the question reducing the results as possible:

For this month MDX query:

SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].&[201101] ON 1 FROM [HOLEFOODS]

I have:

But I want to see not 11,568.43, but the revenue got in the last day sale of January, like what I have for a day query:

SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[DaySold].&[62122] ON 1 FROM [HOLEFOODS]

So I need the last available values for days but in a scope of months. Is it clearer now?

If you ask me why do I need that, it's much more evident with the example of stocks trading.

Consider you have a database of transactions of stocks trading for every day and you want to show in DeepSee the history of the price for some share the months' scale.

You would definitely want to use for the value of the price of the month the price of the last trading day of the month. Makes sense?

How to manage this in DeepSee? So I tried to put it in the scope of HoleFoods.

score 0 · Answer 6 · 2017-04-09T08:00:13-04:00

That is what I understood from your original post. The query I suggested in my first answer works should do and it works for me, see the screenshot. If it doesn't and you are sure you have a last day of the month with revenue in your data try to troubleshoot it by simplifying the query. For example, start by removing .LASTCHILD from the calculated dimension.

score 0 · Answer 7 · 2017-04-10T11:30:24-04:00

Hi, Alessandro!

I did that. CurrentMember works fine:

But if I add Lastchild it shows nothing.

I believe it is the matter of the version. My $zv shows 2016.2 (Build 627U).

I'll update Caché to the latest release and will try it again. Thanks!

score 2 · Answer 8 · 2017-04-10T11:56:36-04:00

I did not see any problem in previous versions so I do not think that will help.

I suspect the problem might be due to the Week dimension that you added to the model! From this post:

"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."

score 0 · Answer 9 · 2017-04-10T12:28:04-04:00

Yes, you are right, that was the issue!

I introduced another hierarchy without the week level and now it works fine!

Thank you, Alessandro!

score 0 · Answer 10 · 2017-04-10T11:56:12-04:00

I did not see any problem in previous versions so I do not think that will help.

I suspect the problem might be due to the Week dimension that you added to the model! From this post:

"A week level in the same hierarchy as day and month will lead to unexpected results. If a week period is needed the week level should be placed in a different hierarchy than the rest of the levels."