Yes The first point is very straight forward and possible.
1. You want to know number of Subscriptions in some particular time period. Say the time period is between Ref_StartDate, Ref_EndDate. Add these two items as "Parameters"
2. Create a new Filed "Count" with the equation: IF (([Start Date]>=[Ref_StartDate] and [Star tDate]<=[Ref_EndDate]) or ([EndDate]>=[Ref_StartDate] and [EndDate]<=[Ref EndDate])) then 1 else 0 end
this will give you the answer to the first question and for second one you can replace startdate with EndDate and try, i am about to leave office otherwise would have shared a workbook with you.
Thank you for your answer.
I have tried putting the paramters and it works well.
Here I want a single universal filter (which is used for other charts as well in the dashboard). So I want to use the range date filter and fetch the starting and ending date of THAT filter in the calculation field.
Other than putting a separate parameter which is an extra work to do (we change filters range quite often so using a filter and a parameter for a single view is not efficient).
Is there a way to accomplish it with a single date range filter?
"You want to know number of Subscriptions in some particular time period."
No I dont want that.
I want to use the filter to get the from very first day till that starting date of filter.
Then from ending date of filter to the very end date of database
In other words, excluding the date range, 0 to date range start and date range end to very last date
and yes, I want make use of the existing filter
You can set a filter to EXCLUDE, and it will eliminate whatever is selected and leave the rest.
That will not solve the problem. I just explained a very simple case, and exclude will not solve it.
I need to use the existing date range filter's starting and ending date in a calculated field. Its a must.
Is it possible?
By the way, As I mentioned, I want to use one filter for many charts. Lets call it universal filter. so using exclude for only one chart will mess up other charts in the dashboard.
That is one reason i really want to use it in a calculated field! Please help
The problem with a filter is that it will either eliminate the rows that are outside the range, or (if you do EXCLUDE) will eliminate the rows within the range. That's what filters do.
Parameters were mentioned, but you have nixed that approach. It's the approach I would take.
If you have robust data (at least one row for every possible date) you can do a calc to get the MAX date and MIN date on the sheet, and that would get you the specific range the user selected. (If you are missing some dates and the user selects a date that doesn't have a row in the data, MAX or MIN will give you the next highest/lowest value, but not the specific date the user selected.) Again, though, this will eliminate the rows that the filter eliminates.