3 Replies Latest reply on Feb 11, 2016 8:36 AM by Shinichiro Murakami

# Get number of days from the date range filter/slider to use in the report

Is there a way to get the number of days from the date range slider (filter) and use it in a calculation?

The date range slider originates from a single date dimension.

This number, whenever the report user slides/moves the date slider, should be supplied to the report for calculations.

Example: A date range slider on a "Time Sheet report" for a company of 3 employees. Each employee works 8 hours a day (measure).

Data from the database:

Emp123 worked 8 hrs each on 1/25/2016, 1/26/2016, 1/27/2016,1/28/2016

Emp135 worked 8 hrs each on 1/25/2016, 1/26/2016, 1/27/2016,1/28/2016, 1/29/2106

Emp146 worked 8 hrs each on 1/25/2016, 1/27/2016

Slider: Start date 1/25/2016... End date 1/29/2016

Report should show:

Empl_Name, Total_Anticipated_Work_Hours, Actual_Work_Hours

Emp123, 5 days x 8 = 40 hrs, 4days x 8 = 32 hrs

Emp135, 5 days x 8 = 40 hrs, 5days x 8 = 40 hrs

Emp146, 5 days x 8 = 40 hrs, 2days x 8 = 16 hrs

Slider changed to: Start 1/27... Ends 1/29

Report should show:

Empl_Name, Total_Anticipated_Work_Hours, Actual_Work_Hours

Emp123, 3 days x 8 = 24 hrs, 2days x 8 = 16 hrs

Emp135, 3 days x 8 = 24 hrs, 3days x 8 = 24 hrs

Emp146, 3 days x 8 = 24 hrs, 1days x 8 = 8 hrs

• ###### 1. Re: Get number of days from the date range filter/slider to use in the report

Kumar,

Here you go, but as a note, if there are days which no employees worked, filter cannot show that dates.

[Anticipated HRs]

(window_max(max([Date]))-(window_min(min([Date]))-1))*8

[Act_hours by EMP]

{fixed [Emp ID]:count([Date])}*8

* Don't forget to add "Context Filter" on "Date" filter  //  Colored grey if context added

This helps LOD calc work correctly after filtered.

Thanks,

Shin

9.0 attached.

• ###### 2. Re: Get number of days from the date range filter/slider to use in the report

If the working dates range was from 1/14/2016 to 1/29/2016, which includes weekends, can I get the total number of days excluding weekends. In the new date range (1/14 - 1/29), expected days to be returned is 10 days.

• ###### 3. Re: Get number of days from the date range filter/slider to use in the report

If the discussion came into the part of excluding weekends, holiday something like that,  That require completely different  challenge.

We need full packaged of the assumption.  And solution becomes pretty complex and takes time.

If this is one time request, you can handle by formula to exclude specific Dates.

Thanks,

Shin