8 Replies Latest reply: Jun 6, 2016 10:08 PM by lakshmi arayaa

# Date Issue

Hi all,

I face a problem in Current Year and Previous Year numbers.

Please find the attached word document for the same.

• ###### Re: Date Issue

Possible way of calculating this could be (Only calculation part of set analysis written below) :

Current Year current Month = Year={"\$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"},Month={"\$(=DATE(FLOOR(MonthStart(max(Date))),'MMM'))"}

Previous year Current Month =

Current year YTD = Year={"\$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

Previous year YTD= Year={"\$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

• ###### Re: Date Issue

Thanks for you repli.

But Date fields are mandatory in my expressions.

Is there any work around with the expressions with "Date" fields??

• ###### Re: Date Issue

//Not Sure if we are setting year and month restriction in expression ,then why date restriction is //also required as we are comparing full month values and not particular days.

Current Year with Current Month

=num(sum({<Date={">=\$(=DATE(FLOOR(YearStart(MAX(Date))),'MM/DD/YYYY'))<=\$(=DATE(FLOOR(Yearend(MAX(Date))),'MM/DD/YYYY'))"}

,FIN_MONTH_NO={"\$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}Exit_Count)

/

((sum({<FIN_YEAR_NO={"\$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

,FIN_MONTH_NO={"\$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}OpeningBalance)

+

sum({<FIN_YEAR_NO={"\$(=DATE(FLOOR(YearStart(max(Date))),'YYYY'))"}

,FIN_MONTH_NO={"\$(=DATE(FLOOR(MonthStart(MAX(Date))),'MMM'))"}>}ClosingBalance))/2),'##.#%')

Previous Year with Current Month

=num(sum({<Date={">=\$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'MM/DD/YYYY'))<=\$(=DATE(FLOOR(Yearend(YearStart(max(Date))-1)),'MM/DD/YYYY'))"}

/

((sum({<FIN_YEAR_NO= {"\$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

+

sum({<FIN_YEAR_NO= {"\$(=DATE(FLOOR(YearStart(YearStart(max(Date))-1)),'YYYY'))"}

• ###### Re: Date Issue

Does this completes your requirement ?

• ###### Re: Date Issue

hi Pulkit,

no it doesn't work

• ###### Re: Date Issue

Try this .

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

Previous Year = =Year(addmonths(max(Date) ,- 1))

Previous Month = Month(addmonths(max(Date) ,- 1))

• ###### Re: Date Issue

Difficult to troubleshoot problems like these by just looking at images, would you be able to share a sample to look at?

Preparing examples for Upload - Reduction and Data Scrambling

• ###### Re: Date Issue

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

Previous Year = =Year(addmonths(max(Date) ,- 1))

Previous Month = Month(addmonths(max(Date) ,- 1))