IBM Business Analytics

 View Only

  • 1.  IBM Cognos Calculation

    Posted Fri June 02, 2023 09:50 AM

    I am re-creating the Data Module in Cognos Analytics from my PowerBi Data Module. I have a measure I created in PowerBi but don't know to to implement in Cognos. need some help.

    The DAX measure in PowerBi is : 

    Ventes AAD =
        CALCULATE([Ventes PlageDate],
            DATESBETWEEN(dim_Calendrier[Date],
                DATE(YEAR(TODAY()), 1, 1),
                DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))-1))
    * where [Ventes PlageDate] is another measure  : Ventes PlageDate = SUM(fact_Ventes[TOTAL_AMOUNT])

    So it is calculating the sales made between 2 dates. 
    How can I rewrite this in a Cognos Calculation? the Ventes PlageDate, I already created the calculation for it in Cognos using total(...)
    Thank you
    -Kayed





    ------------------------------
    Kayed Chehade
    ------------------------------


  • 2.  RE: IBM Cognos Calculation

    IBM Champion
    Posted Fri June 02, 2023 02:55 PM

    I'm not familiar with your expression syntax in PowerBI, but what I think I see is your calculated dates are (1) the first day of the today's calendar year and (2) yesterday's date.

     

    Expressions in Cognos syntax would be:

     

    1. _ADD_DAYS(CURRENT_DATE,((_DAY_OF_YEAR(CURRENT_DATE)-1)*-1))
    2. _ADD_DAYS(CURRENT_DATE,-1)

     

    If you were trying to use these together in a "I only want the dates between, and including, this range" expression, it would be:

     

    [YOUR DATE FIELD TO FILTER UPON] BETWEEN (<Put First Expression Here>) AND (<Put Second Expression Here>)

     

    Note: there are a number of ways to calculate the first expression above, so there's various options to get the first day of the current calendar year.  Expression two is pretty straightforward and probably the go-to manner for calculating yesterday's date (or doing +/- days on any date for that matter).

     

    Logan

     




    Original Message


  • 3.  RE: IBM Cognos Calculation

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

    Hi Kayed,

    You should have a look to relative date analysis. With this feature, you can easily query year-to-date measure.

    The following filters are predefined and can be used:

    • Prior week
    • Prior month (not available in the retail calendar)
    • Prior quarter
    • Prior year
    • Current week
    • Current month
    • Current quarter
    • Current year
    • WTD (week to date)
    • MTD (month to date)
    • QTD (quarter to date)
    • YTD (year to date)
    • Prior WTD
    • Prior MTD
    • Prior QTD
    • Prior YTD
    • Same week last year
    • Same month last quarter
    • Same month last year
    • Same quarter last year
    • Same MTD last quarter
    • Same MTD last year
    • Same QTD last year

    Best regards,



    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    IBM Champion
    ------------------------------

    Original Message