CODEWITHSUNDEEP
cognos
Dinesh Kuma Mani
Dinesh Kuma Mani

Reputation: 1

Cross Table Issue in Cognos Report Studio

Calculating Growth in crosstab Cognos. I need to bring the previous year's value to the current year column. Please help me.

-322,129,132.49 should be coming the PY1 column in the Year of 2018.

enter image description here

Upvotes: 0

Views: 35

Answers (1)

dougp
dougp

Reputation: 3087

Missing information:

  • version of Cognos
  • sample data
  • column names
  • What should PY1 be for 2017?

...but I'll try.

I think what you are saying is that the 2017 PY1 should be 0 and 2018 PY1 should be -322,129,132.49. If that is correct...

Adjust your query to return PY1 in the correct year. This may involve multiple queries joined together. Here is sample SQL to consider:

select a.[year]
, a.unnamed_code_value
, coalesce(b.PY1, 0) as PY1
, a.en_Amount
from (
    select [year]
    , unnamed_code_value
    , sum(en_Amount) as en_Amount
    from table1
    group by [year]
    , unnamed_code_value
) a
left outer join (
    select [year] + 1 as 'year'
    , unnamed_code_value
    , sum(en_Amount) as PY1
    from table1
    group by [year] + 1
    , unnamed_code_value
) b on b.[year] = a.[year]
   and b.unnamed_code_value = a.unnamed_code_value

Upvotes: 0

Related Questions