Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "kdmarkee"){kind=avatar}
I'm hoping what I wish to achieve is possible. It is two fold, per the image I'm attaching. Basically, we are trying to (a) predict when we'll receive first payments in the door based on when we have received first payments in the last 6 months and (b) calculate how much we actually received by that predicted date, if anything. Columns A through F are easily pulled from the data model. Each row in my data model will have a Release Date and may or may not have a First Payment Date depending of course if we received any payments yet. When I have both dates populated, I can then calculate that row's number days (the lag) between them, which is critical to achieve my results in the attached image.
(1) Problem 1 is Column G (Avg Days) (in pink) - This seems to only populate when there is at least one row in my underlying data model with a First Payment Date populated, even though I want to get an avg of all data rows in the last 6 months from the row's Release Date. For row 3, which hasn't received any payments yet, I don't quite understand why I cannot get the info from the previous 6 months to get my Avg # of Days for this column.
I have this set analysis in place for column G:
Avg(TOTAL<Group, Payor, Plan, [Release Date]>{$<[Release Date]={'>=$(=AddMonths([Release Date], -6>)) <=$(=[Release Date])'}>}LagDays_RelToFirstPaymt)
(2) Problem 2 is Column I (Actual Collections) (in yellow)- How can I use the date generated in column H in my set analysis for this column? I tried to use the expression column name from column H in my set analysis but it is not working (maybe that is not allowed in a set analysis statement??).
Thanks!
.png "Former Employee"){kind=icon}
