Max Date of current Quarter if date column has date till future

[sunainapawar]

Hello All,

I have one small scenario. I have a date column which has end date  till future like 01-02-2040, 03-02-2045.

This is basically a contract end date. So this can have future values. Now i have to get the max date of Current Qtr i.e,., 

2021-2022 . Max date should be like till today. So whichever Contracts have end date of current Qtr till today(26-05-2021) must be shown.

I know how to get a max based on date filed but since End date has future dates, not sure how to get the count of contracts expired till date.

Please assist.

[canerkan · Accepted solution]

Hi Sunainapawar,

i am not sure if this is what you are looking for, but try making two more fields in the load script. For Quarter and Year of EndDate

• Ceil(Month(EndDate)/4) as Quarter
• Year(EndDate) as Year

and then simply use these fields to compare with today's quarter and year in set analysis like:

Count({$<Quarter = {"=Ceil(Month(Today())/4)"}, Year = {"=Year(Today())"}>}ContractID)

Regards,

Can

[sunainapawar]

Hello Canerkan,

Thanks for the response, Actually i was looking for same. I solved it using today() in the expression.