Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Get the Value of Previous Business Day in Cognos Report

    Posted Fri May 03, 2024 04:03 AM

    Hi All,


    We are using IBM Cognos Analytics with Watson (11.2.0).

    In our database, we have this one summary table for total disbursement amount by transaction date, product type(internal facility name) and business day flag. Below are the sample snapshot of the table from SSMS:

    In Cognos Report, the table will be tabulated as below example;

    The user will be able to select the date from the date filter to get the total disbursement amount for each business day. Here are some high level rules to get the amount from the date filter;

    1. if transaction date = date_filter and business day flag = Y, then MTD_disburse_amount
    2. if transaction date = date_filter and business day flag = N,  get MTD disburse_amount from previous working day (business_day_flag = Y)
    3. if transaction date = date_filter and business day flag = Y, skip even if T-1 = Y

    Is there a way to achieve this by using the data items value in Cognos Report?

    Any help is deeply appreciated. 



    Thanks and regards,
    Aiman



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------


  • 2.  RE: Get the Value of Previous Business Day in Cognos Report

    Posted Fri May 03, 2024 09:32 AM

    Hi Aiman,

     

    I need some clarification please.

     

    1. In the database table, are there also entries where the 'business_day_flag' is 'N''?
    2. Will the value of the 'transaction_date' in the table always be the last day of the month when the 'business_day_flag' is 'Y'?
    3. I could not follow the logic of your rules. The conditions for rule 1 and rule 3 are the same. What don't I understand?

     

    Regards,

     

    Adriaan

     

      




    Original Message


  • 3.  RE: Get the Value of Previous Business Day in Cognos Report

    Posted Sun May 05, 2024 10:01 PM
      |   view attached

    Hi Adriaan,

    For your queries:

    1. Yes, there are also entries where the 'business_day_flag' is 'N'
    2. Nope, it is based on calendar business day. Some months is Y some months is N
    3. please ignore the third rule first. currently we are not able to get the rule 2.

    I also added some sample data in excel for your usage/testing.

    Regards,

    Aiman



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------

    Attachment(s)

    xlsx
    sample.xlsx   10 KB 1 version
    Original Message


  • 4.  RE: Get the Value of Previous Business Day in Cognos Report

    Posted Tue May 07, 2024 06:16 AM

    Aiman,

     

    This may work:

    1. Your query must contain the transaction date.
    2. Filter the query on the condition 'transaction date <= date selected by user.
    3. There must be an item in the query that returns the latest date where the value of the business day flag is 'Y'. Let us call the date Max_Yes_Flag_Date. It will look something like this:

    max(case

                   when business_day_flag = 'Y'

                        then transaction_date

                  else cast('19000101',date)

             end

      for report)

    1. You can try to filter the same query on Max_Yes_Flag_Date = transaction date or use this query as input for a second query. On the second query still use the filter  Max_Yes_Flag_Date = transaction date.

     

    This will work if there are always transaction dates where the business day flag = 'Y' included in the data.

     

    In the code above I accepted that the transaction date is of the type of date. If it is not, the else part of case statement will change.

     

    Regards,

     

    Adriaan

     

     

          




    Original Message


  • 5.  RE: Get the Value of Previous Business Day in Cognos Report

    Posted Wed May 08, 2024 03:48 AM

    Hi Adriaan,

    Thank you for your kind reply. However, is it possible to visualise your proposed solution? I am quite confused especially when it comes to item no 3 and 4.

    Thanks and regards,

    Aiman



    ------------------------------
    Mohamad Aiman Arif Mohamat Saat
    ------------------------------

    Original Message


  • 6.  RE: Get the Value of Previous Business Day in Cognos Report

    Posted Fri May 10, 2024 05:52 AM

    Hi Aiman,

     

    I created an example which is based on the Excel data that you sent. The Excel sheet was uploaded to our Cognos server.

     

    I had to modify my code to get it to work:

     

    Max_Yes_Flag_Date

    maximum (

         case

                   when [business_day_flag] = 'Y'

                        then [transaction_date]

                    else _make_timestamp ( 2000, 1, 1  )

           end

        for report)

     

    There are four screen prints attached to this message:

    1. The items in the query – 'Y max date query'
    2. The code of the item Max_Yes_Flag_Date – 'Max yes flag date'
    3. The date prompt page – 'Selected prompt date'
    4. The output of the query for a selected date of May 7, 2024 – 'Query output'

     

    It was possible to put both filters on the same query, so a second query is not necessary.

     

    Regards,

     

    Adriaan




    Original Message


Related Content