The question:

Based on the Cognos Analytics gosales sample database. Assuming there are relationships between the various tables in this sample database, e.g. ORDER_HEADER – ORDER_DETAILS, ORDER_DETAIL – PRODUCT, PRODUCT – PRODUCT_TYPE, PRODUCT_TYPE – PRODUCT_LINE.

If the measure [Quantity] is defined as:

[gosales_sample].[ORDER_DETAILS].[QUANTITY]

Define [cy quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2005-04-01 and 2006-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

And [py quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2004-04-01 and 2005-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

Putting the 3 measures in a list report and adding a dimensional level for [PROCUCT_LINE_EN] gives:
QUANTITY       cy quantity        py quantity     PRODUCT_LINE_EN
866234           326012              199094            Camping Equipment
99400              38952                22420               Golf Equipment
301958           143254              20728               Mountaineering Equipment
557854           154568              253020             Outdoor Protection
389908           140642              91884                Personal Accessories

//Henk

The question:

Based on the Cognos Analytics gosales sample database. Assuming there are relationships between the various tables in this sample database, e.g. ORDER_HEADER – ORDER_DETAILS, ORDER_DETAIL – PRODUCT, PRODUCT – PRODUCT_TYPE, PRODUCT_TYPE – PRODUCT_LINE.

If the measure [Quantity] is defined as:

[gosales_sample].[ORDER_DETAILS].[QUANTITY]

Define [cy quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2005-04-01 and 2006-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

And [py quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2004-04-01 and 2005-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

Putting the 3 measures in a list report and adding a dimensional level for [PROCUCT_LINE_EN] gives:
QUANTITY       cy quantity        py quantity     PRODUCT_LINE_EN
866234           326012              199094            Camping Equipment
99400              38952                22420               Golf Equipment
301958           143254              20728               Mountaineering Equipment
557854           154568              253020             Outdoor Protection
389908           140642              91884                Personal Accessories

//Henk

The question:

Based on the Cognos Analytics gosales sample database. Assuming there are relationships between the various tables in this sample database, e.g. ORDER_HEADER – ORDER_DETAILS, ORDER_DETAIL – PRODUCT, PRODUCT – PRODUCT_TYPE, PRODUCT_TYPE – PRODUCT_LINE.

If the measure [Quantity] is defined as:

[gosales_sample].[ORDER_DETAILS].[QUANTITY]

Define [cy quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2005-04-01 and 2006-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

And [py quantity] as:

case when

     [gosales_sample].[ORDER_HEADER].[ORDER_DATE]   

                between 2004-04-01 and 2005-03-31

     then [gosales_sample].[ORDER_DETAILS].[QUANTITY]  

end

Putting the 3 measures in a list report and adding a dimensional level for [PROCUCT_LINE_EN] gives:
QUANTITY       cy quantity        py quantity     PRODUCT_LINE_EN
866234           326012              199094            Camping Equipment
99400              38952                22420               Golf Equipment
301958           143254              20728               Mountaineering Equipment
557854           154568              253020             Outdoor Protection
389908           140642              91884                Personal Accessories

//Henk