Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Find last month with data

Posted on 2010-09-03
11
Medium Priority
?
951 Views
Last Modified: 2016-02-15
I am trying to create an MDX query that will return the last month with data from the cube.  The problem is that there are zeroes loaded into the months where there is no data.  I have attached the query I am trying to use but it always returns the last month of the year and shows 0 as the measure.
SELECT { parallelperiod([Fiscal Year FMP].[Fiscal Year].[FY],1,closingperiod([Fiscal Year FMP].[Fiscal Year].[Mth], [Fiscal Year FMP].[Fiscal Year].defaultmember)),closingperiod([Fiscal Year FMP].[Fiscal Year].[Mth], [Fiscal Year FMP].[Fiscal Year].defaultmember) } ON COLUMNS ,

{ DESCENDANTS( [FMP_Facility].[FMP_Facility].[Level ID].&[STATION].&[V20], [FMP_Facility].[FMP_Facility].[Division] ) } ON ROWS  

FROM [FMP] 

WHERE ( [Category].[Category].[Cat Desc].&[112 ARS NONCUM COLLECTIONS/CUM COLLECTIONS], [Measures].[Numerator] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Open in new window

0
Comment
Question by:vincem1099
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 7

Expert Comment

by:d507201
ID: 33601360
Wow, good stuff.  I've gotta learn something about MDX queries.
0
 
LVL 7

Author Comment

by:vincem1099
ID: 33619972
It would be better stuff if I could figure out how to determine the last month without a 0 loaded into the data.
0
 
LVL 9

Expert Comment

by:radcaesar
ID: 33631048
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 7

Author Comment

by:vincem1099
ID: 33631216
That solution is for a 12 month period not for the last period with data
0
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 33637151
0
 
LVL 7

Author Comment

by:vincem1099
ID: 33663532
That will find the maximum amount but not the last period without 0 for data
0
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 33681115
Under SQL I would try something like:

SELECT MAX(Case when Var ne 0 then Month else 0 end) as Last_Month

Not sure if the same will work for MDX.
0
 
LVL 7

Author Comment

by:vincem1099
ID: 33717561
I think a filter function needs to be used but I am having trouble with the syntax.  This code gives me the error that  a level expression is expected but  a tuple was used:
SELECT { parallelperiod([Fiscal Year FMP].[Fiscal Year].[FY],1,closingperiod(filter([Fiscal Year FMP].[Fiscal Year].[Mth],[Measures].[numerator]<>0), [Fiscal Year FMP].[Fiscal Year].defaultmember)),closingperiod([Fiscal Year FMP].[Fiscal Year].[Mth], [Fiscal Year FMP].[Fiscal Year].defaultmember) } ON COLUMNS ,

{ DESCENDANTS( [FMP_Facility].[FMP_Facility].[Level ID].&[STATION].&[V20], [FMP_Facility].[FMP_Facility].[Division] ) } ON ROWS  

FROM [FMP] 

WHERE ( [Category].[Category].[Cat Desc].&[112 ARS NONCUM COLLECTIONS/CUM COLLECTIONS], [Measures].[numerator] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Open in new window

0
 
LVL 7

Accepted Solution

by:
vincem1099 earned 0 total points
ID: 34537096
Apparently the data cube was updated while this question was open so that the default member for the month hierachy was adjusted to the current month so this MDX query returns the current month with data without having to address the original problem:
 
SELECT { [Fiscal Year FMP].[Mth].defaultmember } ON COLUMNS ,

{ DESCENDANTS( [FMP_Facility].[FMP_Facility].[Level ID].&[STATION].&[V20], [FMP_Facility].[FMP_Facility].[Division] ) } ON ROWS  

FROM [FMP] 

WHERE ( [Category].[Category].[Cat Desc].&[112 ARS NONCUM COLLECTIONS/CUM COLLECTIONS],[Measures].[numerator] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Open in new window

0
 
LVL 7

Author Closing Comment

by:vincem1099
ID: 34618301
Problem resolved by Cube Data changing.  Original problem never really resolved.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question