Author Topic: Line Diagram - How to "identify" months?  (Read 315 times)

Offline CognosUser222

  • Full Member
  • ***
  • Join Date: Oct 2020
  • Posts: 7
  • Forum Citizenship: +0/-0
Line Diagram - How to "identify" months?
« on: 27 Jan 2021 01:31:35 am »
Hello all,
I would like to achieve the following in Framework Manager:

I have a time dimension. Its hierarchie consists of year, half-year, quarter and month.
E. g. 2021 --> 1. Half-year of 2021 --> Quarter 1 2021 --> January 2021.
For the month, the business key is "1" (and not something like "202101") and the member caption is "January 2021". It is important for my client that the member caption says "January 2021" and not just "January".

Now, my client wants to draw a time diagram to compare some measure in different years. The x-axis should consist of the months January, February, ... , December. There should be for each year a different-colored line. But if I take the months into "x-axis", Cognos does not identify "January 2021" with "January 2020" with each other because of the different names.

Can you tell me how to achieve this? Is there some kind of "trick"?

Best,
CognosUser222

Offline bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 476
  • Forum Citizenship: +45/-0
Re: Line Diagram - How to "identify" months?
« Reply #1 on: 27 Jan 2021 05:47:37 am »

You don't say much about what columns are available.

The general pattern would be to have your member caption (either directly, in the expression for the member caption, or indirectly, in the expression of the query item used to create the member caption) have an expression similar to:

 {query item used to provide the month name value} || ' ' || { however you got the year value for its member caption}

Like this:
[Business view].[Time].[Month]  || ' '|| [Business view].[Time].[Year] 

You might need to deal with casting the year value to string and using substring functions to extract just the year part of the string.  There's also the extract function; as I said, it all depends on what you have available.


Quote
Cognos does not identify "January 2021" with "January 2020" with each other because of the different names.

Not quite sure what you mean by that. Can you explain pls.

Offline CognosUser222

  • Full Member
  • ***
  • Join Date: Oct 2020
  • Posts: 7
  • Forum Citizenship: +0/-0
Re: Line Diagram - How to "identify" months?
« Reply #2 on: 27 Jan 2021 06:53:21 am »
Thank you for your answer!

Yes, my member caption currently has a definition similar to [Business view].[Time].[Month]  || ' '|| [Business view].[Time].[Year] . And it displays (correctly) "January 2021".

Now, I want to draw a line diagram. I want to have on the x-axis the months January to December, and for each year a line which displays how some measure changed over the year.
So, a red line for the year 2019, a blue line for 2020 and so on. All those lines should be in one diagram, and above each other. So if the measure for January 2019 is 500 and for January 2020 is 1000, I want on the x-axis "January" and above that, the red line should be at 500, the blue one at 1000.
I found on the Internet such a diagram: http://www.allindiaexams.in/reasoning/data-interpretation/line-graph/23 (Question 111).
Now, if I do a "Visualization" in Cognos, put "Months" into the x-Axis, year into "Color" and my measure into "Measure", my x-axis consists of "Jan. 2019", ..., "Dec. 2019", "Jan. 2020", ..., "Dec. 2020". So the measures for January 2019 and January 2020 are not on top of each other, one is on the right to the other.
If my member caption displays only the month and not the year, the diagram works as expected.

Is this understandable?

Thank you!
CU222