Nov 05, 2019 at 05:23 AM

How do I set up a decision process based on the second most recent value in WEBi report?

53 Views

I have a data set which has transaction details and I need to categorize current service prioritization based on the last exit destination response that i can use in a cross tab table to aggregate clients on their return status.

Client - Entry Date - Exit Date - Exit Destination - Prioritization Status
1 - 1/1/2019 - 10/1/2019 - Home - n/a
1 - 10/15/2019 - null - - Return from home discharge
2 - 6/15/2019 - 7/5/2019 - Home - n/a
2 - 7/15/2019 - 7/31/2019 - Rehab Facility - n/a
2 - 8/30/2019 - 9/15/2019 - Home - Return from Institutional setting

I have a minus query set up to identify returning clients as opposed to new clients that works beautifully, but i need to make the binding decision structure to identify client 1 as a return from home discharge and peg that status to him or her until he or she has a new exit destination response that will supercede the Exit Destination recorded on 10/1. Same with client 2, If the report ran between the 7/15 re-entry and the 8/30 re-entry, the exit that occured on 7/5 would have categorized him as a Home return, but his 7/31 exit to Rehab categorizes him as an institutional return, so he qualifies for different services.

I have a variable (think it's set as a dimension, at the moment, but I've set it as a detail and a measure, too) which returns exit destination for the second entry that returns a null for every row except the second and it is returning the correct exit destination for the second most recent entry. When I try to filter the cross tab table on this, it returns zero clients (there should be several hundred unique clients with anywhere between 2 and 100s of entries, each.) I also cannot use this variable as a row or column in the cross tab.

I know i'm missing something fairly obvious.

Other information that you need to know, The universes are locked down so I cannot modify them.

Help???

Thanks

E