Trick

[smilingjohn]

Hi All,

Can someone please explain me what this below expression is doing ?

=If

(

  IsNull(MIN({Filter} Date)) = 0,

  ,

  Only({<yearMonthSort = {"$(=Date(AddMonths(Today(), -1), 'YYYYMM'))"}>}Sales_StartDate)

)

[Anil_Babu_Samineni]

I am not the good explain person, But i will try

Here how this work is - Need to fetch date where null value become as Zero then we need to ignore for state over here and then Fetch minimum date of those null dates and then fetch only Sales_StartDate where the condition works which as YearMonthSort filter is last month of date should get and which format should and applicable as YYYYMM

Finally, It will return as Sales_StartDate where last month and where the Date field has null values for filter

[smilingjohn]

Thanks Anil

and what does this means ?

From_Date>=Date(MakeDate(Year(AddYears(Today(),-2)),Num(Month(Today()))),'YYYY-MM-DD');   ?

what is -2 for ? here

[vinieme12 · Accepted solution]

=If

(

  IsNull(MIN({Filter} Date)) = 0,    /// if Min(Date) with given {filters} is NOT  null()

  ,  // Then

  Only({<yearMonthSort = {"$(=Date(AddMonths(Today(), -1), 'YYYYMM'))"}>}Sales_StartDate)

)

          // Get Sales_StartDate for Previous month

But using only() means you are trying to retrieve only a single value? or do you want to use Min() to get first Sales_StartDate for previous month ?

[Miguel_Angel_Baeyens]

The -2 is a parameter to the AddYears() function, which subtracts 2 years to the current year as specified by Today(), so the whole expression reads:

"Values in From_Date field greater than or equal to 2015-04-01"

[Anil_Babu_Samineni]

Here,

Year(AddYears(Today(),-2))

This part will return -- 2015(Because, From Today's year we want to show last 2 year data that means 2017 - 2 = 2015)

Num(Month(Today())))

This part will return in Number format of Today's month

Finally, Makedate will return YYYY, MM, DD Format which means 2015, 04, 01 (Here Makedate consider the first day in default)

Date(MakeDate(Year(AddYears(Today(),-2)),Num(Month(Today()))),'YYYY-MM-DD')

And, Finally this will return and change it as 2015-04-01 due to Date function.

And, I highly recommend you to split and start from your end by help of Text object you will get to know.

Helps to you.

[Miguel_Angel_Baeyens]

Only() will return one single value if there is only one single value. Otherwise will return null.

[vinieme12]

Yep, which is why I suggested using Min() instead