Based on From and To Date how to Calcuate PYTD/PMTD in WEBI 3.1

[Former Member]

Hi Guys,

Please suggest me how to create a Formula which I can able to access Previous Years and Month Data In the report.

Report Parameters are like bellow:

Start Date: 01/01/2010

End Date: 07/09/2010

1. Requirement are like below.

a. Eliminate Weekends and Holidays

b. Calculate YTD and PYTD/PMTD based on current Year Date Range mention above

c. Once calculate YTD and PYTD then need to eliminate out of office hours (Ex: 5pm to 8am it will be 16hrs)

d. Now we get Total Number of Hours need to Convert into Days

e. Units Sold for YTD and PYTD need to Divide to take average for each units sold.

Ex:

St Date: 01/01/2010

Ed Date: 20/01/2010

Take of Holidays and Weekends: For instance I take off 7 days

Total Days After eliminating weekends and Holidays would be: 14 days it include 20th day

Now Time to Calculate working hours for this 14 days.

Units Sold: 100 in 14days with working hours.

Again calculate avg.hours or days for each unit sold.

Finally calculate YTD and PYTD Calculation.

Please suggest me guys.

[Former Member]

Hi Ram,

I think first you have to identify the number of working days.

So do you have time dimension in your universe? Or do you have day object which gives you days as per the date like Sunday,Saturday.

Also another question to you is how will you identify whether date is holiday or not!

Now in case you have time dimension or day object then you can apply logic like

no_of_working_days = sum(case when day in ('Saturday','Sunday') then 0 else 1)

Now you can have do remaining calculations from this derived field.

I hope this will help you else come up with your problems.

Thanks,

Swapnil

[Former Member]

Hi Swapnil:

1. I don't have Time Dimension In Universe

2. Based On Country Calendar Holidays Need to Eliminate Holidays

3. yes, I have Day Object Which can be populate from Mon-Sunday

Thanks

ram

[Former Member]

If you don't have a calendar/time dimension, where are your country calendar holidays stored?

[Former Member]

Hi Ram,

If you have day object then as shown in my previous thread you can create a logic to derive the number of working day object and use same in your report.

Thanks,

Swapnil