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

[former_member213525]

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!

[TammyPowlas]

Thank you for your question

Please note I changed your tag to Semantic Layer

Please be more careful when selecting your tags.

[daniele_tiles2]

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)

[former_member213525]

Hello Daniele,

Thanks for the update, I tried what you suggested and it does not parse in IDT.

(CAST(CAST(LEFT([DVR_YearMonth.YearMonth],4) AS INT) -1 ) AS VARCHAR(4)) + RIGHT([DVR_YearMonth.YearMonth],2)

Error::[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'CAST', expected 'AS'.

[daniele_tiles2]

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