MDX function for Year1 - Year2

[CognosAdmn]

Hello All,

This may be a very simple expression for most MDX developers. I am fairly new to MDX and am stuck with this query. Any help is greatly appreciated.

I have to create a crosstab report based on a Transformer Cube package. This report contains a "Group" field along the columns. The "Year" field is nested underneath the "Group". Currently we only have two years in the database (year1 & year2). The rows contains about 30 measures.


                                    
                                Group 1                        Group 2                       Group 3                         Total      
                    2015   2016   Diff           2015   2016   Diff           2015   2016   Diff           2015   2016   Diff
-------------------------------------------------------------------------------------------------------------------------------------------
Measure 1         1      9    -8                 5         2       3                  3         9      -6                 9       20   -11
Measure 2         3      7    -4                 7         4       3                  4         9      -5               14       20     -6
Measure 3         5      5      0                 9         6       3                  5         7      -2               19       18      1


I am trying to add a third column, called "Diff", next to the years column. This new column needs to calculate the difference between Year1 and Year2.

I am not able to figure out the mdx expression to calculate difference of the years.

This Cube contains about 6 dimensions, one of the being the Date Dim. The Date Dimension contains a single level, which is the year. All 30 measures are coming from the transformer cube.

Thank you in advance for your help.


-Shiyam

[MFGF]

Hello All,

This may be a very simple expression for most MDX developers. I am fairly new to MDX and am stuck with this query. Any help is greatly appreciated.

I have to create a crosstab report based on a Transformer Cube package. This report contains a "Group" field along the columns. The "Year" field is nested underneath the "Group". Currently we only have two years in the database (year1 & year2). The rows contains about 30 measures.


                                    
                                Group 1                        Group 2                       Group 3                         Total      
                    2015   2016   Diff           2015   2016   Diff           2015   2016   Diff           2015   2016   Diff
-------------------------------------------------------------------------------------------------------------------------------------------
Measure 1         1      9    -8                 5         2       3                  3         9      -6                 9       20   -11
Measure 2         3      7    -4                 7         4       3                  4         9      -5               14       20     -6
Measure 3         5      5      0                 9         6       3                  5         7      -2               19       18      1


I am trying to add a third column, called "Diff", next to the years column. This new column needs to calculate the difference between Year1 and Year2.

I am not able to figure out the mdx expression to calculate difference of the years.

This Cube contains about 6 dimensions, one of the being the Date Dim. The Date Dimension contains a single level, which is the year. All 30 measures are coming from the transformer cube.

Thank you in advance for your help.


-Shiyam

Hi,

Can you be more specific about the requirement here? Currently your Year level has only two members - 2015 and 2016, so it would be easy to subtract one from the other. What is the plan moving forward? Will there be three year members next year? If so, is the requirement to display all three, or just the last two, or the first and the last? If all three, how would the "difference" be calculated?

The simple solution as things stand is to drag in the 2015 and 2016 members separately (rather than the Year level) and add a calculation with the expression [2016] - [2015]

If you want the latest year and the prior year, then you'd use expressions in query calculations for this instead of dragging in the members:

Latest year - closingPeriod([your Year level])
Prior year - prevMember(closingPeriod([your Year level]))

You can then just subtract one from the other in another query calculation.

Obviously this isn't an option for more than two years, or where you want the first and last year. It's not clear exactly what the requirement is, though?

MF.

[CognosAdmn]

Thank you MF for your reply. This is very helpful.

This is a temporary one time report I am creating. Therefore, both solutions you provided work perfectly.

But to answer your questions: The client always compares the two most recent years. In case they need to compare eg. 2014 against 2016 next year, we could provide a prompt for both years. I am guessing similar to your first suggestion we could calculate [p_year1] - [p_year2] ??!!

Thank you again for your help.