I have a requirement to display Order Volume/# of products where the definitions are:
Order Volume - # of Orders
# Products - count distinct Products [I have aggregate and rollup aggregate set to Count Distinct on the Product Set]
The crosstab columns need to display Selected Period Prompt Values (tree prompt with Year, Quarter, Month, Week, Date hierarchy), YTD, PYTD and YTD - PYTD values.
I have been able to achieved this for Period Prompt values but am unable to get the YTD, PYTD values to come up because of the count distinct.
My YTD is defined as:
filter(
[Model].[ Date].[ Date].[ Year],
[Model].[ Date].[ Date].[ Year].[ Year Code] =
#timestampMask( $current_timestamp , 'yyyy')#
)
My PYTD is defined as:
periodsToDate(
[Model].[ Date].[ Date].[ Year],
item([Last Year Current Date],0)
)
where
Last Year Current Date is:
filter(
[Model].[ Date].[ Date].[ Date],
[Model].[ Date].[ Date].[ Date].[ Date Code]
=
#timestampMask ( _add_years($current_timestamp,-1) , 'yyyy-mm-dd' )#
)
My YTD column that I am displaying on the crosstab are defined as aggregate(currentmeasure within set [YTD]) and so on for PYTD.
My PYTD - YTD column is a simple measure with YTD - PYTD calculation.
The presence of aggregate(replacing with count distinct also does not help) causes an issue for count distinct with Cognos complaining that count distinct cannot be used against crosstab or OLAP.