Skip to Content

Hot calculate YTD, Current Month, Previous YTD in IDT?

Hello Experts,

I am looking for following formulas in IDT and I have a field YYYYMM in mydata model.

YTD-

Previous Year YTD-

Current Month-

Previous Current Month-

Complete year- for example Jan 2018 to Dec 2018

Q1, Q2, Q3, Q4 -

QTD -Quarter to date for example 201904 to 201904

Previous 3 years- 2016, 2017, 2018

Thanks in Advance!

Add comment
10|10000 characters needed characters exceeded

  • Thank you for your question

    Please note I changed your tag to Semantic Layer

    Please be more careful when selecting your tags.

  • Get RSS Feed

1 Answer

  • 6 days ago

    Hi Sam,

    I'm not sure I've understood what you mean, but I guess you can easily calculate the values required considering the functions of the underlying DB.

    For example, to get the PREVIOUS YTD in SQL Server, the function in IDT could be:

    (CAST(CAST(LEFT([YOUR_DATA],4) AS INT) -1 ) AS VARCHAR(4)) + RIGHT([YOUR_DATA],2)
    Add comment
    10|10000 characters needed characters exceeded

    • Hi Sam,

      I wrote it without the chance to check it (as an example that needs to be implemented), but to me it seems that the AS VARCHAR(4) has to be put inside and near the -1.

      HTH

      Daniele Tiles