Struggling with Date Rages

[marsbar]

Hi All,

Im really struggling with the following, hoping someone can help:

I have a large table with a "Job Created" column. I want to find out how many jobs have been created by month (or date range specified by the user. I have created another "Date Filter" table with simply START DATE and END DATE, hoping users can set the start and finish date range to see which jobs were created within the time stated.

I also would like to show the amount of jobs as a COUNT for the dates entered. I have tried creating a slice formula but have failed badly... Any ideas please?

Thanks!

[WillowMobileSys]

To get the list of rows, you want to test that BOTH  [Start Date] <= [Job Created] AND [Job Created] <= [End Date].  The syntax of the expression would depend on where and how you are using it.

Since you are using a Slice I will presume that you want a static filtered view that the user may go into many times over a period of time to see the same set of rows...until the filter dates are changed.

I will also assume that the Date Filter table has row entries by each user, identified by their login email,  so they can specify a personal date range.

In this case, your Slice should be based on the table where the [Job Created] columns lives.  The expression would be something like:

AND( [Job Created] >= ANY(SELECT(Date Filter[Start Date], [User] = USEREMAIL())),
     [Job Created] <= ANY(SELECT(Date Filter[End Date], [User] = USEREMAIL()))
)

If any of the assumptions are wrong and you still need help, please respond back here.

I hope this helps!

[marsbar]

Hi - thanks very much for this... sorry but Im still struggling🤔

So I have the main table "Database" with the [date created] column within it. I also have the basic table I was hoping to use as filter dates "Date Filter"  with [Start date], [End date] & [Count].

I have created a slice "Filter Date" for the table "Database" table with the row filter condition: 

AND( [date created] >= ANY(Date filter[Start Date]),
[date created] <= ANY(Date filter[End Date])
)

...I've set up a UX as a form "Filter Date View" which shows the [Start date], [End date] & [Count] boxes. When I edit the dates, nothing changes on the count.

Ive also set up a UX "Jobs in Month" using the slice "Filter Date", hoping this would show only the jobs that fall in the date range - again this doesn't work for me.

Sorry, Im very probably over complicating the issue and am definitely tying myself in knots!

Please can you add any clarity to this? Thanks