Cognos Analytics

Cognos Analytics

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


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only

  • 1.  Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/18/23 10:16 PM

    Hi All,


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

    Currently in our data module, we have a fact table and working_date table. By right we will join this two table (fact.transaction_date = working_date.date) to calculate the total amount for given working days. Inside the working_date table we only have 2 columns, date and business_day_flag (Y as business days, N as non business days).





    From the same data module I have created a report with static date prompt. Please refer to snapshot below for reference. 



    This is how I calculate the disburse amount with regards of working days from the Data Item Expression.

    if ([C].[C_TDF_DM].[Sheet1].[transaction_date] in_range ?date_filter2? and [C].[C_TDF_DM].[Sheet1_1].[business_day_flag] = 'Y')

    then ([C].[C_TDF_DM].[Sheet1].[disbursement_amount])

    else (0)


    I have unlocked the page to create a custom header. Can refer snapshot below as reference.



    Now we have the challenge to calculate the working days based on the date_filter and put them inside the table list header. Is there a way to map the date_filter to a separate date table or something and calculate the working days from there? 

    Currently I can only get the total of days from first of months till date selected only. I just need to add a condition which check whether any date within the range (first of months till date selected) and their business_day flag = Y and total them up using the report expression for the table list header.




    Any help is deeply appreciated. 



    Thanks and regards,
    Aiman



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


  • 2.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/19/23 12:40 AM
    I might be way off here in understanding all the context you gave. But I thought you (1) have business_day_flag on the data module youre using for your date prompts, and (2) youre trying to count the number of business_day_flag = Y for the range of dates within your selected (or defaulted) prompt values. Can you not write a count function within the same query, or another query?

    Logan Whitaker

    PMO, HEB Supply Chain Planning & Operations

    o: 210.938.6043 m: 210.429.3181

    e: whitaker.logan@heb.com







  • 3.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/19/23 03:26 AM

    Hi Logan, 

    Count / Running-count / Total is all available only for Data Item Expression. What I'm trying to do is on the Report Expression specifically for the table list header. Inside the Report Expression there is no count or total function that I can use.



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

    Original Message


  • 4.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/19/23 03:52 AM

    Hi Mohamad,

    you have already unlocked the page layout and inserted multiple items into the list header. Why don't you set the source of one of them to Data item value and pick the desired calculation to show the value?

    I then see a calculated value right in the header:



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 5.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/19/23 11:48 PM

    Hi Robert,


    I try changing from Report expression to Data item value. Yes it does give me the correct working days on the table list header, but somehow my table rows will be duplicated. 


    I create separate Data Item to check the date according to date filter, another data item to get the current working days and another data item to get the last working day of month. But since i have two different date prompt, my table row will be duplicated. Instead of all is summed up according to the facility code, each row is unique with referencing to different dates. Is there any other workaround?





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

    Original Message


  • 6.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/20/23 02:01 AM
    Edited by Robert Dostal 04/24/23 03:25 AM

    Hi Mohamad,

    the next solution that comes to my mind is to use a singleton connected to a separate query. Unlock your page object and put it into the list headeer.
    I tested it with our data as we also have a central date table with working day flag linked to all fact tables and it works.

    My fact query calculates the amount like yours with an "if" that checks the date field with in_rage ?date? and work_day=Y. The second query has just one column "count(WORK_DAY)" and two filters: "WORK_DAY_FAL=Y" and "transaction_date in_ragen ?date?". The query pushed to the db will filter all dates from the fact table between the selected dates which are workdays. The result then just has to be counted or summarized depending on your data.

    Can you try this approach?



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


  • 7.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/21/23 11:41 AM

    Hi Robert,

    Can you elaborate more on this? I would love to try this out.

    Thanks, regards.



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

    Original Message


  • 8.  RE: Map Static Date Prompt to a Custom Table and Calculate the Working Days

    Posted 04/24/23 04:50 AM

    Hi Mohamad,

    I use two queries:

    the fact query is linked to the list, the other to the singleton. both queries have one filter applied to both:


    The fact query gives all measure data from the range of the selected period and the workday query will count the workdays by filtering on the flag additionally. Our flag is 0/1 instead of N/Y.
    With unlocked page objects you can place a singleton into the header and drag the count(workdays) query item from the workday query into it.
    Run the report.

    Of course, you have to adopt the calculation and filters for your calendar table that contains your workdays. The requirement is that four fact table is joined to your calendar table in your data model



    ------------------------------
    Robert Dostal
    Team Leader BI
    GEMÜ
    Ingelfingen
    ------------------------------

    Original Message


Related Content