Topic
  • 1 reply
  • Latest Post - ‏2013-04-25T17:53:23Z by OpherB
BhagatS
BhagatS
150 Posts

Pinned topic Split months value by years?

‏2013-04-25T09:42:29Z |

Hello, I have a chart, where i want to display months on the x-axis and years on the y-axis.

I have a cube that has All Dates Dimension that consists of All Dates Hierarchy that has levels: Year, Quarter, Month & Week.

How can I use these items to create a chart which displays Months Jan, Feb...Dec and plots a line for each year?

I have attached a sample output of what is expected.

Thanks in advance

Attachments

  • OpherB
    OpherB
    664 Posts
    ACCEPTED ANSWER

    Re: Split months value by years?

    ‏2013-04-25T17:53:23Z  

    The short answer is "you cannot".

    This often comes up in discussions of different views of time. The standard hierarchy (Years, Quarters, Months, Weeks, Days) is monolithic, meaning there is no constituent part which is not in the hierarchy. There is no "February", but there is "February 2011", "February 2012", February 2013", etc.

    Yes, you could make some wild MDX set() definition to select all January's to make a "January" set and the same with all other months. But it will be much easier (and faster and not require maintenance forever) to create a new dimension (I would call it "All Months") which has just one level with all 12 month names listed. Drop that Dimension into your x-axis and the " Year" level of Time into the y-axis and you're all set.

    Good luck,
    Opher

    PS: Note that a new Dimension would be needed, for example, if you wanted to compare Day of Week (Monday, Tuesday, etc.) for the same reasons.

  • OpherB
    OpherB
    664 Posts

    Re: Split months value by years?

    ‏2013-04-25T17:53:23Z  

    The short answer is "you cannot".

    This often comes up in discussions of different views of time. The standard hierarchy (Years, Quarters, Months, Weeks, Days) is monolithic, meaning there is no constituent part which is not in the hierarchy. There is no "February", but there is "February 2011", "February 2012", February 2013", etc.

    Yes, you could make some wild MDX set() definition to select all January's to make a "January" set and the same with all other months. But it will be much easier (and faster and not require maintenance forever) to create a new dimension (I would call it "All Months") which has just one level with all 12 month names listed. Drop that Dimension into your x-axis and the " Year" level of Time into the y-axis and you're all set.

    Good luck,
    Opher

    PS: Note that a new Dimension would be needed, for example, if you wanted to compare Day of Week (Monday, Tuesday, etc.) for the same reasons.