This seems obvious and basic, but does anybody know how to populate a grid like I have pasted below in an ad-hoc analysis that you could then create a column chart off of? Trying to simply show year over year change in say, gross revenue, by month. I get an error message that you can’t have members from the same dimension on both the row and column.
Because of the way the OLAP restrictions - you have the message “Row and Column Axes Cannot Contain two or more hierarchies form the same dimension”.
You can work around this - using a Template, and two distinct analysis.
Use one filtered for 2018, and one filtered for 2019 (or named sets to make it variable for other years).
You can link the “pages” in the template so you can interact with your customer/location dims.
You can’t add graphs into Template Studio templates anymore, but you could have this sent to you via Report Binder, or export to Excel quickly for adding the Column Chart.
Hope that helps.
Tim
Yes, thanks Tim. This was my next strategy, although as you point out I won’t be able to graph it and post it to a dashboard. Apparently we’ll be able to do charting in Template Studio in the next release, which I thought I saw somewhere was coming on 3/8/19.
David,
I’ve done what Tim has suggested many times, but my other go-to less elegant approach is to use one dataset, hide it, and use cell references.
In my mock-up example, I have a dataset with time dimension for three years. Unfortunately, to make this work, you can’t use reference to .leaf. You have to specify each member 2018M01, 2018M02 and so on.
Rows 7, 8 and 9 are all cell references. When finished, hide the dataset rows.
Again, not elegant, but solves the problem (and just one example of using hidden data to get the desired end result).
BTW - Prophix Winter update was out on Friday - with charting in Template Studio!!!
Another alternative is to use the Version dimension for the years (rows). So in the column, you’d put the monthly dimension members. Then, set up calculated versions for prior year (PY). The MDX in the calculated version would look like the following:
([Versions].[Default].&[ACT],ParallelPeriod([Time].[Default].[Year],1,[Time].[Default].CurrentMember))
I was going to put in my 2 (US) cents in, but then we upgraded to version 9 and Template Studio makes it so much easier. I am glad to see that Prophix keeps imprroving.
David,
Denise’s MDX suggestion got me thinking of how to do this using the time perspective dimension.
I have a solution, but someone with more MDX experience might be able to give a better (and more efficient) formula.
The catch is figuring out how to use MDX to call other periods without using the time dimension. There are MDX formulas that designed for time (parallel period, endperiod, lag), but they call on the time dimension. That’s fine, until you want to have a column and row using time dimension.
To get around this, I created calculated member in the time perspective dimension.
For the same month, 12 months earlier, I created this calculated member:
SUM(Lastperiods(13,[Time].[default].currentmember),[Perspective].[default].&[base]) - SUM(Lastperiods(12,[Time].[default].currentmember),[Perspective].[default].&[base])
It basically sums the prior 13 periods and 12 periods, and subtracts them to get the 12 month. Someone with more MDX experience might be able to to give a better formula. I suspect that it might be resource intensive if the account you are wanting to analyse has complicated formulas behind it.
You can then create additional calculated members substituting the number of months (25 and 24 to get 24 months ago).
In adhoc analysis (or Template Studio), select time as your column, such as 2018.leaf. Select Base and the calculated time perspectives as your rows.
Here is an example (we have a November year-end):
To get the month without the date, using custom member properties.
As is usual with Prophix, the issue is not usually “it can’t do it”, but which way to do it. There’s usually a solution to everything you want to do in Prophix.
I use a custom time perspective member with the following MDX to get the prior period, which I would think could be adapted to get the period 12 months ago quite easily:
([Time Perspective].[Default].&[BASE], [Time].[Default].LAG(1))
One thing to note about the “prior period” logic above is that it is sensitive to the level of your time selection; if you’re looking at a year, it will show the prior year; if looking at a month, it shows the prior month, etc. In my case I want it this way.
You could make sure it always shows “12 months ago” regardless of the level you’re looking at by adapting slightly:
([Time Perspective].[Default].&[BASE], CLOSINGPERIOD([Time].[Default].[Month], [Time].[Default]).LAG(12))
