Please post sample data or workbook along with expected results.
How you're getting the count of 2 for filled in the sample workbook ?
I really appreciate your help! I have spent the last 4 hours trying to understand why this doesn't work for my real data set.
I replicated your calculations and viz, but to no avail:
Say if I know in Nov 2018 I had 10 filled positions and 100 applications received overall,
When I'm using 'Application Date' as dimension, I have 100 applications, but less than 10 filled positions (because it's only counting positions that were filled as a result of applications received in Nov 2018)
When I'm using 'Position Filled Date' as dimension, I have less than 100 applications, but 10 filled reqs (because it's only counting applications received towards positions that ended up being filled)
To reiterate, I wanted to see total volume of applications for that month (for positions that ended up being filled as well as those still open), and total volume of positions filled.
I am attaching a workbook that has a portion of the data that I'm working with that I scrambled, but it illustrates my predicament well.
Here are the expected results:
Here's what I'm getting:
Please help me!
Thank you so much for your time, this solution worked for me!
I understand the general concept of LODs, but what you've got here is some nested LOD magic. If you have a chance, could you please explain in a couple of sentences the approach you used here? Knowing myself I will try and reverse engineer your calculation to try and understand the solution instead of just copy pasting/ adjusting it.
I am new here, is there anything else I can do besides <This helped me> and <Correct Answer> to appraise your help?
1 of 1 people found this helpful
As you have two different column for dates and you're using application date in the view, therefore in order to get the count wrt to position filled date, first you've to remove the application date and add the position filled date in the view.
Exclude is removing the application date and Include is adding Year and Month of position filled date to view. All this happen behind the scenes. Finally Countd expression count the Distinct Position ID wrt to Year and month of position filled date. This can also be solved with Fixed instead of Include and Exclude but fixed will not respond to normal dimension filtes if you're using in the view. Then you've to add the filters to context in order to get the correct count. To avoid this I used Include and Exclude which respond to normal dimension filters.
Finally Outer IF condition compare the application and position filled date to show the count in respective row.
Hope this helps.