IBM Business Analytics

 View Only

  • 1.  Calculating Pay Period End Dates

    Posted Mon December 14, 2020 11:24 AM
    ​Hi,

    I have a request to create Pay Period End Dates in one of my reports.  We use Oracle as our DB.  I have not been able to find a simple or efficient solution in either oracle or Cognos.  But I have found a solution in Power BI.  Unfortunately, I have never used Power BI before.  Therefore, I do not know how to convert the Power BI solution to Cognos.  Does anyone know how to convert the following two solutions to Cognos?

    pay period number = FLOOR(DATEDIFF(DATE(2017,4,29),'Table'[Date],DAY)/14,1)+1

    pay period end date = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[pay period number]))

    Thanks

    ------------------------------
    Debbie Schiller
    ------------------------------


  • 2.  RE: Calculating Pay Period End Dates

    IBM Champion
    Posted Wed December 16, 2020 06:52 PM
    Edited by Logan Whitaker Wed December 16, 2020 07:04 PM

    Hi Debbie,

    The "pay period number" is interesting.  Looks like you are chopping up all 2 week periods since April 29, 2017 into sequential pay weeks since starting operations or something. With the first two weeks being pay period 1 and the most recent two weeks being 95.  Let me know if that's not accurate.  

    Here is a step by step, color-coded breakdown of the components of your existing expression for "pay period number" into a Cognos expression:

    • FLOOR(DATEDIFF(DATE(2017,4,29),'Table'[Date],DAY)/14,1)+1

    CAST('2017-04-29',DATE)

    • FLOOR(DATEDIFF(DATE(2017,4,29),'Table'[Date],DAY)/14,1)+1

    _DAYS_BETWEEN('Table'[Date],CAST('2017-04-29',DATE))/14

    • FLOOR(DATEDIFF(DATE(2017,4,29),'Table'[Date],DAY)/14,1)+1

    CAST(_DAYS_BETWEEN('Table'[Date],CAST('2017-04-29',DATE))/14,INT)+1

    As for the "pay period end date", it looks like your pay periods begin, and include, a Saturday and then end, and include, a Friday two weeks later.  An example on my end is:

    • Saturday, April 29, 2017 ≤ pay period number 1 ≤ Friday, May 12, 2017 ;
    • Saturday, May 13, 2017 ≤ pay period number 2 ≤ Friday, May 26, 2017 ; and so on and so forth.

    Let me know if this is the case, or if it the logic needs to be adjusted.  Depending on our answer, we might be able to come up with something for this expression.

    P.S. - You may want to consider putting these calculations into the underlying database or within the framework.  Reason is if you have a lot of data and you, for example, put this calculation into a report and want to filter for pay period number = 90... the application has to first calculate all the data in the database first before it can then filter on the pay period number you want.  I've encountered this when calculating different timezones and wanting to filter on everything that was, let's say, 9:00am CST.  Takes forever to run.

    Another option is to create a table called PAY_PERIODS in your database with three columns named DATE, PAY PERIOD NUMBER, and PAY PERIOD END DATE.   You can pre-calculate all the values from April 29, 2017 through the next 20 years or whatever (in Excel is easy).  Then insert all those records to the Oracle database table.  Once done, you use the Framework Manager to map PAY_PERIODS.DATE to 'Table'[Date] from above.  This would be a more optimal solution.  

    Hope this helps!


    ------------------------------
    Logan Whitaker
    ------------------------------

    Original Message


  • 3.  RE: Calculating Pay Period End Dates

    Posted Wed September 27, 2023 04:15 AM

    Hey there, Debbie Schiller!

    I feel your pain with the Pay Period End Dates challenge – it can be a real labyrinth, especially when you're delving into new tools like Power BI. Your approach using Power BI is pretty ingenious, I must say!

    Now, converting those Power BI solutions into Cognos might seem like a daunting task, but fear not! I've had some experience with similar conversions in the past.

    In Cognos, you can achieve a similar result by creating a calculated field for the pay period number and another for the pay period end date.

    For pay period number, you can use the following expression:

    (FLOOR((cast([Your Date Column], date) - date(2017, 4, 29))/14))+1

    And for pay period end date:

    _maximum([Your Date Column] for [Pay Period Number])

    I've found that Cognos functions like "cast" and "_maximum" can help mimic Power BI's DAX functions. It might take a bit of tinkering, but with persistence and a dash of experimentation, you'll conquer this conversion challenge in no time.



    ------------------------------
    Alex Ahsras
    ------------------------------

    Original Message