Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD calculation without dates column

Hello All,

I am writing this to get help from the community on calculating YTD, MTD, QTD etc. without having a date field.

Let me elaborate the issue, I have month number, year, day of month etc stored in separate columns but i wont be able to concatenate and make that into a date column as i have days numbered more than 31 days.i am following Fiscal calendar and the number of days in a particular month could vary between 1 to 31 for some months and 1 to 35 days for the rest.

Any help is highly appreciated.

Thanks.

1 Solution

Accepted Solutions
Not applicable
Author

The sample calendar looks like the attached one. i am trying to calculate YTD, MTD etc. based on the Fiscal calendar. And the Fiscal start and end dates for each month will vary for every year.

View solution in original post

5 Replies
sunny_talwar

Would you be able to share a sample of how your data looks and the kind of output you are looking for

Not applicable
Author

The sample calendar looks like the attached one. i am trying to calculate YTD, MTD etc. based on the Fiscal calendar. And the Fiscal start and end dates for each month will vary for every year.

Not applicable
Author

Hello,

Did anyone get a chance to look at the attached excel book? I do not know how to get more people to look at this

sunny_talwar

I don't understand the data pasted in your Excel sheet. Can you elaborate what is the input and what is the expected output here

Not applicable
Author

The attached excel is a sample of the calendar which should be used across all the applications in my environment.

Column, "PeriodDT" is the actual date on our regular calendar, but the calculations have to be done considering the Fiscal dates, Fiscal Months and Fiscal Quarters from the excel.

Eg: Assume today is 1 Apr 2016, then the MTD should be calculated from the beginning of the Fiscal Start of Month which would be 28-feb-2016 (On the regular calendar, refer the PeriodDT column in the excel). Similarly, the YTD, QTD calculations are to be calculated based on the FISCAL calendar and not on the regular calendar.

Please let me know if it is possible.