Topic
  • 8 replies
  • Latest Post - ‏2013-12-13T17:26:56Z by shr1489
Suffeli
Suffeli
18 Posts

Pinned topic 12 rolling months by Year

‏2011-03-10T14:54:47Z |
Trying to build a line chart where I have month namens (Jan, Feb, Mar...) on x-axis and years ( 2008, 2009 ,2010) on Series.
Numbers I present on chart should be rolling 12 month figures.

I have cube as a datasouce with regular time dimension where I have month name as a shortname.
Also tried to do separate Month dimension and substring month names from Time dimension.
I considered doing custom rolling 12 month field at Transformer but it needs to be two full years and current so don't know would it be any help.

I have used function:
lastPeriods (12, [Current Month])
where Current Month is currentMember('time_dimension')

Any idea how to do this?

Regards
Suffeli
Updated on 2014-03-25T08:06:37Z at 2014-03-25T08:06:37Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: 12 rolling months by Year

    ‏2011-03-10T22:46:25Z  
    This is probably going to be a bit tricky. I don't have this set up here so the following is a rough approach to doing this type of thing.

    1. Use the setup where you have a regular time dimension and a separate Months dimension (containing just the categories Jan through Dec).
    2. Put the Months from the Month dimension on the ordinal axis
    3. Put the Years from regular time dimension on the series axis
    4. Define the measure for the chart using the following (I'm typing this freehand so this will require some testing/validation on your part):

    aggregate([Measure] within set lastPeriods(12, item(filter(descendants(currentMember([Cube].[Regular Time Dimension].[Regular Time Hierarchy]), [Cube].[Regular Time Dimension].[Regular Time Hierarchy].[Month Level]), caption([Cube].[Regular Time Dimension].[Regular Time Hierarchy].[Month Level]) = caption(currentMember([Cube].[Month Dimension].[Month Hierarchy]))),0))
    


    The general idea is that you pick up the current member from the ordinal axis, the month, and the series, the year. Then you use the name of the month to find the same month at the month level of the regular time hierarchy below the year you have picked up from the series context. Once you have that month under the regular time hierarchy you can apply the lastPeriods function to get the rolling 12 month range and then aggregate the measure for this set of months.
    Updated on 2014-03-25T08:12:56Z at 2014-03-25T08:12:56Z by iron-man
  • Suffeli
    Suffeli
    18 Posts

    Re: 12 rolling months by Year

    ‏2011-03-11T12:34:32Z  
    Thanks Phil,

    This approach assumes that you have same month labels in Regular Time dimension and Month dimension? I change the names so I could test this. Formula returned regular monthly values.
    I am not sure is my Month dimension right one for this purpose? There's only month names, no hieqarchy at all.

    Years and Months on report filters the data so that you can't get rolling figures.

    {code
    aggregate (QUANTITY within set lastperiods(12,item(Filter(descendants(currentmember(Cube.Time.Time),Cube.Time.Time.Month), caption(Cube.Time.Time.Month) = caption(currentMember(Cube.Month.Month))),0))){code}
    Suffeli
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: 12 rolling months by Year

    ‏2011-03-11T21:09:34Z  
    • Suffeli
    • ‏2011-03-11T12:34:32Z
    Thanks Phil,

    This approach assumes that you have same month labels in Regular Time dimension and Month dimension? I change the names so I could test this. Formula returned regular monthly values.
    I am not sure is my Month dimension right one for this purpose? There's only month names, no hieqarchy at all.

    Years and Months on report filters the data so that you can't get rolling figures.

    {code
    aggregate (QUANTITY within set lastperiods(12,item(Filter(descendants(currentmember(Cube.Time.Time),Cube.Time.Time.Month), caption(Cube.Time.Time.Month) = caption(currentMember(Cube.Month.Month))),0))){code}
    Suffeli
    Something will have to be available to match between the months from the two different hierarchies. If you do not have the same caption then you might load the simple month name (without year qualification) into the short name property of the Month level in your regular time dimension. This should give you a property to match to the caption of the month in the Months dimension. You would then use the roleValue function to get the month name from the regular time dimension:

    aggregate(tuple(QUANTITY,rootMember([Cube].[Month].[Month])) within set lastperiods(12,item(filter(descendants(currentmember([Cube].[Time].[Time]),[Cube].[Time].[Time].[Month]), roleValue('_shortName', [Cube].[Time].[Time].[Month]) = caption(currentMember([Cube].[Month].[Month]))),0)))
    


    I've added the tuple function to aggregate expression to override the context of the Month dimension. This should allow you to get all the month data regardless of the context on the ordinal axis. You may need to define the tuple in a separate data item and then reference the new data item in the aggregate expression.
    Updated on 2014-03-25T08:12:50Z at 2014-03-25T08:12:50Z by iron-man
  • Suffeli
    Suffeli
    18 Posts

    Re: 12 rolling months by Year

    ‏2011-03-14T07:31:29Z  
    Excelent!
    Thanks Phil again:-)

    Tuple helped and now it works.

    Regards
    Suffeli
  • Suffeli
    Suffeli
    18 Posts

    Re: 12 rolling months by Year

    ‏2011-05-12T06:50:09Z  
    Hi,

    Tried to change expression like this:

    Original which work ok
    aggregate(tuple([QUANTITY], rootMember([Cube_name].[Month].[Month])) within set lastPeriods(12, 
    item(
    Filter(
    descendants(currentmember([Cube_name].[Time].[Time]), 
            [Cube_name].[Time].[Time].[Month]), 
    caption([Cube_name].[Time].[Time].[Month]) = caption(currentMember([Cube_name].[Month].[Month])))
    ,0)
    )
    )
    


    New one. This returns same figures for each month name
    aggregate(tuple([QUANTITY], rootMember([Cube_name].[Month].[Month])) within set lastPeriods(12, 
    item(
    Filter(
    descendants(currentmember([Cube_name].[Time].[Time]), 
            [Cube_name].[Time].[Time].[Month]), 
    rolevalue('_shortname', [Cube_name].[Time].[Time].[Month]) =  rolevalue('shortname', currentmember( [Cube_name].[Month].[Month])))
    ,0)
    )
    )
    


    Wondering if rolevalue is used in right way in this context.

    Regards
    Suffeli
    Updated on 2014-03-25T08:06:44Z at 2014-03-25T08:06:44Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: 12 rolling months by Year

    ‏2011-05-12T22:21:52Z  
    • Suffeli
    • ‏2011-05-12T06:50:09Z
    Hi,

    Tried to change expression like this:

    Original which work ok
    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">aggregate(tuple([QUANTITY], rootMember([Cube_name].[Month].[Month])) within set lastPeriods(12, item( Filter( descendants(currentmember([Cube_name].[Time].[Time]), [Cube_name].[Time].[Time].[Month]), caption([Cube_name].[Time].[Time].[Month]) = caption(currentMember([Cube_name].[Month].[Month]))) ,0) ) ) </pre>

    New one. This returns same figures for each month name
    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">aggregate(tuple([QUANTITY], rootMember([Cube_name].[Month].[Month])) within set lastPeriods(12, item( Filter( descendants(currentmember([Cube_name].[Time].[Time]), [Cube_name].[Time].[Time].[Month]), rolevalue('_shortname', [Cube_name].[Time].[Time].[Month]) = rolevalue('shortname', currentmember( [Cube_name].[Month].[Month]))) ,0) ) ) </pre>

    Wondering if rolevalue is used in right way in this context.

    Regards
    Suffeli
    Can you create a Transformer model from the Cognos sample data with the dimension structure that you have in your regular model? If so then please attach the model so that I can build the cube here. This will give me something to work with in developing an expression for you. Also, it would help if you didn't wait two months because I tend to forget about these posts fairly quickly.
  • Suffeli
    Suffeli
    18 Posts

    Re: 12 rolling months by Year

    ‏2011-05-16T09:31:06Z  
    Can you create a Transformer model from the Cognos sample data with the dimension structure that you have in your regular model? If so then please attach the model so that I can build the cube here. This will give me something to work with in developing an expression for you. Also, it would help if you didn't wait two months because I tend to forget about these posts fairly quickly.
    Hi,

    Yes, I am sorry that it taken so long to me to test this in practice.

    Attached is desing what I have used in my model.

    I was trying to achieve crosstab like this with 12 month rolling figures.

    January February March April etc...

    2009

    2010 |
    2011 |

    When I was using label and caption function it worked, but it's not possible to change labels so I have to use shortname instead.

    Best Reagrds
    Suffeli
  • shr1489
    shr1489
    1 Post

    Re: 12 rolling months by Year

    ‏2013-12-13T17:26:56Z  
    • Suffeli
    • ‏2011-05-16T09:31:06Z
    Hi,

    Yes, I am sorry that it taken so long to me to test this in practice.

    Attached is desing what I have used in my model.

    I was trying to achieve crosstab like this with 12 month rolling figures.

    January February March April etc...

    2009

    2010 |
    2011 |

    When I was using label and caption function it worked, but it's not possible to change labels so I have to use shortname instead.

    Best Reagrds
    Suffeli

    Hello,

     

    I'm trying to achieve the same Jan to Dec  Months as above. I have a dimensional model. Transformer cube. 

    "If you do not have the same caption then you might load the simple month name (without year qualification) into the short name property of the Month level in your regular time dimension' -- I didn't get this step. Please elaborate on this. :(