Creating calculation using relative measures

[Newb]

Hi Gurus,

Has anyone experienced creating calculation using relative measures (generated when you lookup to a date column that is linked to Gregorian calendar)? Or, maybe configuring a new relative time filters that does basic arithmetic operation between the upper and lower range of #$_this.parent.idForExpression#?

To try illustrating this, I have my _as_of_date parameter is set to 31/5/2022. Also, I've added two more filters in the Gregorian calendar data module:

- Current day: to show as of date
- Prior Month End: to show last date of previous monthimagine I have a data module with table that looks like this:

Imagine the follow data set in my DM..
Date,Product,Actual
31/1/2022,Apple,100
28/2/2022,Banana,200
31/3/2022,Pineapple,300
30/4/2022,Apple,400
31/5/2022,Apple,500

I've linked the date with the calendar module the actual column has relative measures as well.

In report/dashboard, when the columns are pulled into a list/table:
Product, Actual, Current Day [Actual], Prior Month End [Actual]
Apple      ,1000, 500, 400
Banana   ,200,   null, null
Pineapple,300,   null, null

The requirement is to have another column that calculates Current Day [Actual] - Prior Month End [Actual], so the first record should yield 100

But I think Cognos doesn't allow us to do such operation using relative measures. Any ideas how to go about this?

Much appreciated!

[bus_pass_man]

If I correctly understand what you want, you want to create a calculation which determines the variance between two relative time measures.  That's fairly easily done with a stand alone calculation. 

I don't know if you could build the operation into the calendar.  It seems to be for defining windows.  You will notice that the filters are built into the generated SQL similar to this, and I think those are used. 


SELECT
    Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY.RTL_CITY AS Rtl_City,
    SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2021-09-27' AND
                SALES.order_day_date < DATE '2021-10-02'
                THEN
                    SALES.QUANTITY
        END) AS Same_Week_Last_Year__Quantity_,
    SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2022-09-26' AND
                SALES.order_day_date < DATE '2022-10-02'
                THEN
                    SALES.QUANTITY
        END) AS Current_Week__Quantity_
FROM
    Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY
        INNER JOIN SALES
        ON Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY.RTL_LOC_SITE_KEY = SALES.RTL_LOC_SITE_KEY
WHERE
    SALES.order_day_date >= DATE '2022-09-26' AND
    SALES.order_day_date <= DATE '2022-10-02' OR
    SALES.order_day_date >= DATE '2021-09-27' AND
    SALES.order_day_date <= DATE '2021-10-02'


I don't know why the where clause goes away though.

SELECT
    Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY.RTL_CITY AS Rtl_City,
    SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2021-09-27' AND
                SALES.order_day_date < DATE '2021-10-02'
                THEN
                    SALES.QUANTITY
        END) AS Same_Week_Last_Year__Quantity_,
    SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2022-09-26' AND
                SALES.order_day_date < DATE '2022-10-02'
                THEN
                    SALES.QUANTITY
        END) AS Current_Week__Quantity_,
    SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2022-09-26' AND
                SALES.order_day_date < DATE '2022-10-02'
                THEN
                    SALES.QUANTITY
        END) - SUM(
        CASE
            WHEN
                SALES.order_day_date >= DATE '2021-09-27' AND
                SALES.order_day_date < DATE '2021-10-02'
                THEN
                    SALES.QUANTITY
        END) AS Current_Week__Quantity____Same_Week_Last_Year__Quantity_
FROM
    Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY
        INNER JOIN SALES
        ON Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY.RTL_LOC_SITE_KEY = SALES.RTL_LOC_SITE_KEY
GROUP BY
    Retailers__itemNormalGenerated_RTL_LOC_SITE_KEY.RTL_CITY

[Newb]

Thanks bus_pass_man!

The calculation can be created in the DM itself and it only works if I check the 'Calculate after aggregation', which I am having hard time trying to understand the difference ::). Also, if I try creating this calculation directly in report or dashboard, it will give error 'Could not bind [tablename]'.

I think so too that it is not possible to define the calculation into the calendar. I tried changing 'AND' to '-' to indicate deduction but it gives error when trying to use it in dashboard.