Topic
5 replies Latest Post - ‏2013-09-13T17:07:04Z by DanielWagemann
Lloyd_Buss
Lloyd_Buss
19 Posts
ACCEPTED ANSWER

Pinned topic PeriodsToDate function for running total - Need Help

‏2013-09-12T22:28:21Z |

Hi

I have a chart on a report which displays all months for the current year. On the chart I have a cumulative target measure which my client wants to display all 12 months. The second series is my sales measure which I have coded as follows:

Total([Cube].[Measures].[Invoice Sales Value]
within set
[Customer],
periodsToDate ([Cube].[Time].[Months].[Year],currentMember([ScorCube].[Time].[Months])))

The problem with this is I cant get the series to stop at the current month because of the currentMember expression. There are only sales up until the current month but the chart has plotted October, November and December (sames values as September) because of the currentMember expression iterates through the 12 months in the categories.

Can anybody help me get the series to stop at the current month?

Just to give a bit more information, the month categories on the chart come from the children of Year value prompt.

descendants ([Cube].[Time].[Months].[Year]->?pYear?,1)

Thanks
Lloyd

  • DanielWagemann
    DanielWagemann
    850 Posts
    ACCEPTED ANSWER

    Re: PeriodsToDate function for running total - Need Help

    ‏2013-09-12T23:15:14Z  in response to Lloyd_Buss

    I find this one very interesting, but I am not near an install right now, but I have a couple of questions.  How are you determining what the current month is?  (Is it a member in the tree or are you basing it on the current date?)  Also what is your cube source, PC, TM1, SSAS or Essbase/DB2OLAP.

    I will prove this out in the am, but if you are using a PowerCube and the current member is a physical member, we should be able to get away with linked member, intersect and periodstodate expression.

     

     

  • Lloyd_Buss
    Lloyd_Buss
    19 Posts
    ACCEPTED ANSWER

    Re: PeriodsToDate function for running total - Need Help

    ‏2013-09-12T23:38:23Z  in response to Lloyd_Buss

    The source is SSAS 2008 and the current month comes from a named set. So I would reference it in a report like this - lastperiods(9,item([Cube].[Latest Month],0)). The Iatest month named set is created from the [Cube].[Time].[Months] hierarchy.

    I got this on another forum which I tried but didnt work.

    Total([Cube].[Measures].[Invoice Sales Value]
    within set
    [Customer],
    filter(periodsToDate ([Cube].[Time].[Months].[Year],currentMember([Cube].[Time].[Months])),tuple(currentMember([Cube].[Time].[Months]), [Cube].[Measures].[Invoice Sales Value]) > 0))
     

    Its a tricky one!!

    • DanielWagemann
      DanielWagemann
      850 Posts
      ACCEPTED ANSWER

      Re: PeriodsToDate function for running total - Need Help

      ‏2013-09-13T14:45:15Z  in response to Lloyd_Buss

      You ain't kidding, so linkMember as it is only available against PowerCube.  I would have expected a completeTuple approach to work but it does not....  I have been able to put something together against the Cognos SSAS sample cube.  The expression assumes there is a monthkey attribute, preferablly of type integer.

      if (cast([GO Sales Fact].[Time].[Time].[Current Month].[Current Month - Key],integer)>=
      #timestampMask ($current_timestamp,'mm')#
      ) then
      (Null)
      else
      (
      total([Unit Cost] within set
      periodsToDate([GO Sales Fact].[Time].[Time].[Current Year],currentMember([GO Sales Fact].[Time].[Time])))
      )

      So I had to use the cast, which I don't like, hopefully you have a monthkey attribute of type integer.  I added a macro to extract the month based on the current date.  The rest should be pretty clear.  Attached also please find the 10.2 example spec.

       

      Attachments

  • Lloyd_Buss
    Lloyd_Buss
    19 Posts
    ACCEPTED ANSWER

    Re: PeriodsToDate function for running total - Need Help

    ‏2013-09-13T16:15:33Z  in response to Lloyd_Buss

    Hi Dan

     

    Thanks for that, I dont have that database on my vmware but I managed to work out what to do. Still wouldn't work for me, even though I managed to isolate the 2 numbers in the first row of the code in another query and it worked.

    My expression has changed slightly, I discovered I could use the YTD calculated member rather than the currentmember expression.

    If(cast(roleValue ( '_businessKey' ,[Cube].[Latest Month]),integer)>=#timestampMask($current_timestamp,'yyyymm')#)
    Then
    (Null)
    Else
    If(caption([Customer])='Caption')
    Then
    (
    Total([Cube].[Measures].[Invoice Sales Value] within set Set([Member],[Customer]),[YTD])
    )
    Else
    (
    Total([Cube].[Measures].[Invoice Sales Value] within set [Customer],[YTD])
    )

    The error I have is below, oddly enough the report runs without the cast but as soon as the cast goes in it fails.

     

    MDO-ERR-0016
        The level business key '[Cube].[Product Group].[Product Groups].[Level1].[Level1 - Key]' has multiple parts. This is not supported for queries requiring local processing.

     

     

    • DanielWagemann
      DanielWagemann
      850 Posts
      ACCEPTED ANSWER

      Re: PeriodsToDate function for running total - Need Help

      ‏2013-09-13T17:07:04Z  in response to Lloyd_Buss

      Your best bet is to create an integer attribute which represents a numeric value for the YYYYMM