KPI and MDX in Goal-Expression

Hi,

Why does a MDX-Expression like

([Dim Time].[CalendarYear].PrevMember, [Measures].[Sales Amount - Fact Reseller Sales])

not work within a KPI-Goal-Expression ?

Is there a work-around for that?

Best whishes,
Manfred

Hi Manfred,

what does it means "not work within a KPI-Goal-Expression"

Simply you don't see data or you receive an error message?

If you simply don't see data maybe the "CurrentMember" for "[Dim Time].[CalendarYear]" is "All Periods" in your KPI-Goal-Expression context so AS can't find a PreviousMember.

sql

KPI - Sales Trend

Hi,

I am trying to create a Sales Trend KPI, where the value expression is last month sales (Identify last month based on current system date) and target expression is last month previous year sales amount times 1.04.

Is there a way to accomplish this using MDX in KPI.

Thanks,

Ravi

Identifying the lastest month of data is the trickiest part. One technique to do this is to create a calculated member named CurrentMonth that uses the VBA!Date() and VBA!DatePart() to construct a member reference that can then be resolved with StrToMember. Another techinique is to again create a CurrentMonth calculation with a hard coded reference to a date and then update the definition of this calculation each time a new month of data is loaded. (See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/pullingpiecesapart.asp for some help on the VBA functions.)

Once you have this CurrentMonth member, you can use the ParallelPeriod MDX function (http://msdn2.microsoft.com/en-us/library/ms145500(SQL.90).aspx) to calculate the lat month of the previous year.

|||Thanks Matt.

Key time column for time series algorithm

Hi, all experts here,

Thank you very much for your kind attention.

I am confused on key time column selection. e.g, I want to predict monthly sales amount, then what column in date dimension should I choose to be the key time column? Is it calendar_date (the key of date dimension) column or calendar_month?

Thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Key Time is the column that sorts your data in a series and the granularity for the column should be the same as your data. Since in your example you're predicting Monthly Sales, the Key Time should be the calendar_month.

However, the data type for key time is required to be sortable, i.e. long, double or datetime.

|||

Hi, Shuvro,

Thanks a lot for your kind advices.

With best regards,

Yours sincerely,