10 Replies Latest reply on Sep 11, 2018 8:14 AM by Okechukwu Ossai

# How to get the Last Business day and Prior Business day

Hi All, I have a Date field 'Collection Date' it contains all the Business run dates in a moth. Ex: In AUG/2018 we have the date values  08/3, 08/4, 08/5 ,08/10, 08/11, 08/12, 08/21, 08/22, 08/29 with corresponding Defects for each Application. Now I want to create a report that shows only the Latest Business date defects and Prior to Latest Business Date defects. From the above example Latest is 08/29 and prior date is 08/22 Application                            Last Business Day Defects (08/29)              Prior Business Day  Defects (08/22) -----------------                          --------------------------------------------                ------------------------------------------------ ABC                                            200                                                                    300 DEF                                            350                                                                    500 Could anyone please help me to create above 2 calculated fields ? Thanks in Advance. Thanks, Suresh.

• ###### 1. Re: How to get the Last Business day and Prior Business day

Hello Suresh you can check this out.

This is similar to your requirement .

Thanks
Rahul

• ###### 2. Re: How to get the Last Business day and Prior Business day

Hi Suresh,

Try these formula to get latest business date and prior to latest business date. You can then count the number of defects for those dates.

{FIXED [Application]: MAX([Collection Date])}

{FIXED [Application]: MAX(IF [Collection Date] <> [Latest Business Day] THEN [Collection Date] END)}

Hope this helps

Ossai

• ###### 3. Re: How to get the Last Business day and Prior Business day

Hi Ossai, Thanks for your reply. Could you please let me know how to calculate "Last Business Day Defects" and "Prior Business Day Defects". I need both metrics on the report. Thank you so much.  Suresh.

• ###### 4. Re: How to get the Last Business day and Prior Business day

What is the logic behind the 2 metrics? What are you counting? Are you counting them across any particular dimension?

• ###### 5. Re: How to get the Last Business day and Prior Business day

We have a direct db field "Defect Count".Its SUM(Defect Count) by Application as shown below.

From the "Collection Date" Latest date is :08/29 and Prior one is 08/22

Sample Report Data:

Thanks,

Suresh.

• ###### 6. Re: How to get the Last Business day and Prior Business day

I assume it is summing up defect count for those dates? If not, please post sample dummy data.

IF [Latest Business Day] = [Collection Date] THEN [Defect Count] END

IF [Prior Business Day] = [Collection Date] THEN [Defect Count] END

Hope this helps.

Ossai

• ###### 7. Re: How to get the Last Business day and Prior Business day

My dataset looks like as shown below.

Report should display as below.

Thanks,

Suresh.

• ###### 8. Re: How to get the Last Business day and Prior Business day

Hi Suresh,

Here it is. See attached workbook.

You can add [Latest Business Day] and [Prior Business Day] to the Details marks and then insert into Title to make the title dynamic.

Regards,

Ossai

• ###### 9. Re: How to get the Last Business day and Prior Business day

Thank you so much Ossai.It solved my issue.

• ###### 10. Re: How to get the Last Business day and Prior Business day

You are welcome.