Cognos Analytics

 View Only
  • 1.  Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    Posted Thu June 01, 2023 10:55 AM

    I have a scenario where the data is loaded into the data warehouse with a one day lag.  So data is loaded for May 30th on May 31st, May 31st data on June 1st, June 1st data on June 2nd.  We're using the Gregorian calendar to set the lookup reference and enable relative time for the data module measures.  This was working great during testing but now that we're on June 1st, all the Current Month relative time measures show no data.  That is correct since the data has a one day lag.  

    What I would like it to show is the last day of the previous month's data as the Current Month even though we're now on the first day of the current month. Does anyone know how to change the Gregorian calendar to account for this type of situation?

    Thank you.



  • 2.  RE: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    Posted Thu June 01, 2023 01:50 PM


    Just to confirm, you want to have the data for yesterday and before that but run the report today while using relative time.

    If you set the _as_of_date global parameter to use May 31 then that should do what you want.

    https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=date-setting-as-global-parameter

    https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=analysis-customizing-reference-date

    You could use the use yesterday option of the global parameter as well.

    https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=2022-dynamic-as-date-parameter




    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 3.  RE: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    IBM Champion
    Posted Mon June 05, 2023 03:25 AM

    Ian,

    One enhancement that could be made to the dynamic date technique is to use a report to build the global parameter list.

    This report could be included in the deployment that is included within the technique.

    The advantages of using a report are

    1. No scope for errors when someone tries to replicate the parameter list.
      As it is someone need to enter the parameter values exactly as they are for the technique to work.
    2. It would replace the current techniques multiselect parameter lists with a single drop down.
      With the current technique a user can choose multiple time perspectives.
      By using a report this would no longer be the case. It removes user training and user error.

    It also demonstrates that a report can be used for building a global parameter, something that people may not be aware of.

    Any chance you could get the accelerator catalog updated?



    ------------------------------
    Marc Reed
    ------------------------------



  • 4.  RE: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    Posted Mon June 05, 2023 09:24 AM

    I've forwarded this suggestion to the appropriate party.



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 5.  RE: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    Posted Mon June 05, 2023 09:43 AM

    This is how macros for relative dates usually look like.
    You can change second line: 

    let today = substr($current_timestamp, 0, 10);

     into

    "let today = _add_days ( substr($current_timestamp, 0, 10), -1);" in all of them.

    That way you would calculate all relative date filters compared to yesterday, and not to today.


    #

    let calTable = $_this.parent.split.ref;

    let today = substr($current_timestamp, 0, 10);

    let refDate =

        case tolower($[Time Perspective] )

        when 'use yesterday'   then _add_days (today, -1)

        when 'use last sunday' then _add_days (today, '-' +

                                     queryValue(calTable + '.DayOfWeek', calTable + ' .TheDate = ' + today))

        else $_as_of_date

        end;

    let parentId = $_this.parent.idForExpression;

    let low = queryValue(calTable + '.dWeek',

                  calTable + '.TheDate = ' +

                  queryValue(calTable + '.PW_TheDate',

                             calTable + '.TheDate = ' + refDate)

                );

    let high = queryValue(calTable + '.dWeek',

                   calTable + '.TheDate = ' + refDate);

    parentId + ' >= ' + low + ' AND ' + parentId + ' < ' +  high

    #



    ------------------------------
    Milan Milovanovic
    ------------------------------



  • 6.  RE: Using Gregorian Calendar for relative time but need first day of each month to still show last day of prior month as the current month

    Posted Wed June 07, 2023 09:53 AM

    Hi Dong,

    Here is a cheesy way that might work for you: 'clean' the date column in the Data Module.
    Select the date related field and then in the properties pane, edit the expression.
    To shift things by one day, change it to: _add_days ( DATE_FIELD, 1)    or use -1 to go in the other direction.



    ------------------------------
    Kind regards,
    Henk Cazemier
    ------------------------------