Filter results based on another filter's date range

Question

Background:

I have the following user's registration funnel, where user creates account and then goes through prompts with the goal of registering:

id  date        create_account_date user_creates_account registration_date  user_registers 
1   12/30/2017  12/30/2017          1                    12/30/2017         1
2   12/30/2017  12/30/2017          1                    1/2/2018           0
2   1/2/2018    12/30/2017          0                    1/2/2018           1
3   12/31/2017  12/31/2017          1                    12/31/2017         1
4   1/1/2018    1/1/2018            1                    1/3/2018           0
4   1/3/2018    1/1/2018            0                    1/3/2018           1
5   1/1/2018    1/1/2018            1                    1/1/2018           1
6   1/2/2018    1/2/2018            1                    1/3/2018           0
6   1/3/2018    1/2/2018            0                    1/3/2018           1
7   1/3/2018    1/3/2018            1                    1/3/2018           1
8   1/4/2018    1/4/2018            1                    1/4/2018           1

In aggregate:

                      12/30  12/31  1/1  1/2  1/3  1/4  Total  Total 1/2-1/4
User Creates Account  2      1      2    1    1    1    8      3
User Registers        1      1      1    1    3    1    8      5

Issue:

I am trying to add a date filter, where I can pick the date range of the data I want to see.

• I added create_account_date as filter, and picked Jan 2 to Jan 4. However, that will only force min(registration date)='1/2/18', while max(registration date) can happen after Jan 4.

• I also tried forcing create_account_date = registration_date, but that understates those who registered on a day different from create_account_date, but still within the filtered date range.

Ask:

I would like to be able to filter the output by the date range filter/parameter. So create_account_date and registration date per user are >= min(date) and create_account_date and registration date per user are <= max(date). Here create_account_date >= registration date

So with filter implementation I would have:

                      1/2/2018  1/3/2018    1/4/2018    Total
User Creates Account  1         1           1           3
User Registers        0         1           1           2

Thank you in advance.

Answer 1

You are trying in a wrong way don't try to manipulate create account and registration date instead add a filter only for normal date field and place the fields in sheet and see the result.

If you really want to create a filter then you can't add the condition just using normal filter instead you need to create a two parameters.

One parameter for start date

One parameter for end date

For both the parameters use date field to display the list of data

Now create two calcualted fields

If create account date >=[start date parameter] and create account date < [end date parameter]
then 
your field
end

Similarly

If registration date >=[start date parameter] and registration date < [end date parameter]
then 
your field
end

USe both the fileds in rows and place date in column of sheet

Answer 2

I'm not 100% clear on the goal, but it sounds like you want to find users where the account_create_date is between a start and end date AND the registration_date is between a start and end date. If that's so, you could filter with WHERE (create_account_date BETWEEN @start_date AND @end_date) AND (registration_date BETWEEN @start_date AND @end_date). My syntax assumes SQL Server, but you can make it work for other DBMS too.