Hello, I have seen this question answered a few times. Other users suggest using some kind of table calculation and filter on the results. This is supposed to hide the data, and not exclude it, so that it can be used for other calculations. However, this is not working for me. When I tried applying that technique, it still changed my calculation when I "filtered \ hid" the data.
Here is my scenario.
I am trying to visualize some data on applicants to a university. I want to plot the applications coming in over time. To do this, I calculate how many weeks an application is submitted before the start of term. Then I can use this as columns and a running sum of the number of applications at each week interval as rows.
The running sum looks like this. RUNNING_SUM(COUNTD([STUDENT_IDENTIFIER])). I use COUNTD because we don't care about repeat applications, I've already only selected the latest in the SQL query.
This is fine, I can bring START_TERM to the color shelf and look at these trends per term that they are applying. Again, this works great, looks great.
HOWEVER! This is the tricky part. I would like to set a target to reach, so that I can look at how many applications the department should be at on a certain date, to hit the target. I do this by averaging the past 5 years at each week interval (the shape each trajectory is very similar), normalizing and scaling by the target.
I have implemented this in using multiple axes. You can see how that looks in the attached image.
The problem I'm having then, is that I would like to merge/sync these axes, and ONLY show the data for the current term (that's the gold one that has only made it to about -12 weeks since that's the start o the summer term), and the projected path.
But I am running into trouble figuring out how to filter out the other terms, while still using that data to calculate the average of the past terms for the projected target path.
There are a few filters in here that aren't connected ( I want to be able to filter specific degrees or programs) and I've grouped the terms into Term types so that we can isolate Summer, Fall, Spring.
I cannot post the data or workbook since it includes sensitive information, but the image doesn't reveal anything, since the degree/program filter is not showing.
ALSO: If there is a better method of doing this, I am all ears, I tried figuring out a way to do it with analytics but still couldn't get it to only show the current term and projected path.
HELP.JPG 135.5 KB