Questions Creating formula for GI

Acumatica Generic Inquiries & Pivot Tables

Last Post by Royce Lithgo 4 years ago

2 Posts

2 Users

3 Reactions

1,160 Views

RSS

Posts: 8

Nur Syafika

Topic starter

September 26, 2022 2:40 am

(@nsyafika)

Active Member

Joined: 4 years ago

Hi All,

I would like to add column for this status. However, there's might wrong with my coding since i can't found any data for Expired >= 2 Years and Expired <= 3 Months.

Expiry Status:

Expired < 2 Years
Expired >= 2 Years
Expired <= 6 Months
Expired <= 3 Months =IIf([IspkpCompanyOperatorLicense.EndDate] <Today() AND [IspkpCompanyOperatorLicense.EndDate]<DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'y', 2 ), 'Expired< 2 Years', IIf([IspkpCompanyOperatorLicense.EndDate] <Today() AND [IspkpCompanyOperatorLicense.EndDate]>=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'y', 2 ), 'Expired >= 2 Years', IIf([IspkpCompanyOperatorLicense.EndDate] >=Today() AND [IspkpCompanyOperatorLicense.EndDate]<=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'd', 180 ) , 'Expired <=6 Months', IIf([IspkpCompanyOperatorLicense.EndDate] >=Today() AND [IspkpCompanyOperatorLicense.EndDate]<=DateAdd([IspkpCompanyOperatorLicense.EndDate] , 'd', 90 ), 'Expired <= 3 Months', 'False'))))

Thank you

1 Reply

Posts: 557

Royce Lithgo

September 26, 2022 8:48 pm

(@roycelithgo)

Honorable Member

Joined: 6 years ago

Try this:

=IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'y',2), 'Expired more than 2 Years',
IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',180), 'Expired 6 months to 2 Years',
IIf([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',90), 'Expired 3 to 6 Months',
IIf([IspkpCompanyOperatorLicense.EndDate] < Today(), 'Expired less than 3 Months', 'False'))))

I changed your labels so that they made more sense to me. To check for cutoff dates, you need to apply DateAdd formula to Today().

Here's an alternate version using Switch():

=Switch([IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'y',2), 'Expired more than 2 Years',
[IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',180), 'Expired 6 months to 2 Years',
[IspkpCompanyOperatorLicense.EndDate] < DateAdd(Today(),'d',90), 'Expired 3 to 6 Months',
[IspkpCompanyOperatorLicense.EndDate] < Today(), 'Expired less than 3 Months',
True,'False'))))

12 Forums
2,533 Topics
11 K Posts
49 Online
2,438 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

By using this website, you agree to our Terms of Use (click here)