Topic
  • 3 replies
  • Latest Post - ‏2013-09-18T18:13:00Z by OpherB
HiepHuynh
HiepHuynh
2 Posts

Pinned topic get value measure of last Year same month

‏2013-09-18T04:50:52Z |

Hi all,please help me!

I'm developing a report by Crosstab which rows is a list months (ext: 201212 --> 201311) and 2 column (value measure of current YYYYMM, value of last YYY(Y-1)MM)

I can get value measure of column 'Current Actual Measure', but I can't get value of column 'Prev Actual measure' although it has data of previous Year (201112 -->201211).

[MONTH] CURRENT MONTH
[Current ACTUAL Measure] [Previous ACTUAL Measure] (last Year but the same month)
201212 2,673                    2,662
201301 1,972                    2,263
201302 2,939                    2,183
201303 2,783                    3,258
201304 3,109                    3,081
201305 3,696                    3,651
201306 3,241                    3,058
201307 4,117                    3,218
201308 4,314                    4,036
201309                      3,363
201310                      3,194
201311                      3,808

In Time hierarchy below as: <<Time_hierarchy.PNG>>

Thanks a lot,

HiepHuynh

Attachments

Updated on 2013-09-18T04:57:20Z at 2013-09-18T04:57:20Z by HiepHuynh
  • OpherB
    OpherB
    664 Posts

    Re: get value measure of last Year same month

    ‏2013-09-18T06:59:32Z  

    The problem you have here is that the Time Dimension as you have it does not support the report you are trying to build.

    You have a row for "201212" and you want it to return two values - this year and last year. So you get them: This year is 2673 and last year is NULL! There is no intersection between the set of data points in 201212 and 201112. NULL.

    What you want is another Dimension for All Months, which is just 01-12 month numbers. Then in the crosstab you put All Months as the rows, 2012 and 2011 from the current Time Dimension as the columns and "Actual" as the measure. Done.

    Good luck,
    Opher

     

     

  • HiepHuynh
    HiepHuynh
    2 Posts

    Re: get value measure of last Year same month

    ‏2013-09-18T09:01:10Z  
    • OpherB
    • ‏2013-09-18T06:59:32Z

    The problem you have here is that the Time Dimension as you have it does not support the report you are trying to build.

    You have a row for "201212" and you want it to return two values - this year and last year. So you get them: This year is 2673 and last year is NULL! There is no intersection between the set of data points in 201212 and 201112. NULL.

    What you want is another Dimension for All Months, which is just 01-12 month numbers. Then in the crosstab you put All Months as the rows, 2012 and 2011 from the current Time Dimension as the columns and "Actual" as the measure. Done.

    Good luck,
    Opher

     

     

    Thanks Opher,

    In database,as if i have enough data from 201101--> 201311.

    when i push item [month] into row and 2 items ([Current Actual], [Prev Actual]) into column, this mean is cognos server will query [2 measures] with condition is [month], this get [Current Actual] by [month] and [Previous Actual] by [month] too. So i want to edit condition from [month] become ([month]-100) for [Previous Actual], but unsuccessful..

    I tried to edit expression definition of [Previous Actual] below as:

    aggregate([measure] within set periodsToDate ([Time].[Time].[Fiscal_Year], [Time].[Time].[Calendar_Month]))

    But i don't understand detail about periodsTodate(), i did also research information of this.

    Do you have any way?

    HiepHuynh,

    Updated on 2013-09-18T09:12:39Z at 2013-09-18T09:12:39Z by HiepHuynh
  • OpherB
    OpherB
    664 Posts

    Re: get value measure of last Year same month

    ‏2013-09-18T18:13:00Z  
    • HiepHuynh
    • ‏2013-09-18T09:01:10Z

    Thanks Opher,

    In database,as if i have enough data from 201101--> 201311.

    when i push item [month] into row and 2 items ([Current Actual], [Prev Actual]) into column, this mean is cognos server will query [2 measures] with condition is [month], this get [Current Actual] by [month] and [Previous Actual] by [month] too. So i want to edit condition from [month] become ([month]-100) for [Previous Actual], but unsuccessful..

    I tried to edit expression definition of [Previous Actual] below as:

    aggregate([measure] within set periodsToDate ([Time].[Time].[Fiscal_Year], [Time].[Time].[Calendar_Month]))

    But i don't understand detail about periodsTodate(), i did also research information of this.

    Do you have any way?

    HiepHuynh,

    I don't understand what the issue here is. You have included a graphic that shows the data, so it appears you got the report to run. What is the problem?