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.  Special Working Day Calculation

    Posted 07/02/20 09:58 AM
    Hello all,


    I'm looking for a solution to a very specific problem in calculating a time period.

    I have a calendar that defines working days. For the calculation of a time period only based by working days, I created an additional table in Framework Manager, which contains the date, the working day "yes / no" flag and a count using the "running count" function as a fact. I filter the table by working day = yes.

    So far everything works fine. The calculation results are correct.
    -------------------------------------------------- -----------------------------------
    But and now I come to the real problem.
    It is about the duration of transports measured in working days. As a rule, these transports start and end on working days. Unfortunately, ship transports also take place. Ship transports also start / end on Saturdays, Sundays and public holidays.

    By filtering by working day = yes, I suppress these days so that a transport does not appear in the result set in the report if it starts and / or ends on a non-working day.

    If I remove the filtering, the function "running count" also calculates a value for non-working days and the calculation of the transport time is incorrect if a non-working day is between the start and end.
    -------------------------------------------------- -------------------------------------------------- -------
    I am looking for an option how I can build up a continuous counting of days in the Framework Manager, in which the rule is that if "working day = yes", the function "running count" increases the counter by 1 and if "working day = no", the function does not increment the counter.
    Date Working Day Y/N Counter Working Day should be
    Jan 1, 2007 1 1 1
    Jan 2, 2007 1 2 2
    Jan 3, 2007 1 3 3
    Jan 4, 2007 1 4 4
    Jan 5, 2007 1 5 5
    Jan 6, 2007 0 6 5
    Jan 7, 2007 0 7 5
    Jan 8, 2007 1 8 6
    Jan 9, 2007 1 9 7
    Jan 10, 2007 1 10 8
    Jan 11, 2007 1 11 9
    Jan 12, 2007 1 12 10
    Jan 13, 2007 0 13 10
    Jan 14, 2007 0 14 10
    0 - No work running count fct.


    ------------------------------
    Peter Hess
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Special Working Day Calculation

    Posted 07/02/20 10:18 PM
    try like this

    running-count( (if workdayyn = '1') then (1) else (0) )

    i think you already knew how to solve it :)

    ------------------------------
    JinHo Ko
    ------------------------------



  • 3.  RE: Special Working Day Calculation

    Posted 07/03/20 03:18 AM
    Hi JinHo,

    thank you, but it does not works. The expression running-count( (  IF ( [Modell View].[IsWorkingDay].[WorkingDayFlag]  = 1 ) THEN ( 1 ) ELSE ( 0 ) ) ) counts also the non-working days in the continous counting of days. 



    Peter

    ------------------------------
    Peter Hess
    ------------------------------

    Original Message


  • 4.  RE: Special Working Day Calculation

    Posted 07/03/20 03:24 AM
    Ops.. Sorry.. try to like below

    if (workdayYN = '1') then (1) else (null)

    change 0 to null

    because 0 also count.. if value is null, count function ignore null

    ------------------------------
    JinHo Ko
    ------------------------------

    Original Message


  • 5.  RE: Special Working Day Calculation

    Posted 07/03/20 03:51 AM
    Hi JinHo,

    that works! Thanks a lot.

    ------------------------------
    Peter Hess
    ------------------------------

    Original Message


  • 6.  RE: Special Working Day Calculation

    Posted 07/03/20 03:43 AM
    Wouldn't a running-total make more sense?

    ------------------------------
    Chris Turner
    ------------------------------

    Original Message


  • 7.  RE: Special Working Day Calculation

    Posted 07/03/20 03:54 AM
    Hi Chris,

    yes, that works also. Now I have two different Methods. FINE.

    ------------------------------
    Peter Hess
    ------------------------------

    Original Message


Related Content