Measure appearing in a separate row using lag date formulate
I ran into an issue where I have a prior period measure that appears in a separate row when the dimensions change from prior period. In the screenshot attached you can see the loan officer changed and the period balance of 250,000 appears in a separate row. I am using DATAPOINT with lag date of 1 to get prior period balance. I am expecting the prior Bank Balance to appear next to Jane Doe loan officer name which is the current officer on the account. I have found that when I bring in the loan officer name as a DATAPOINT within the formula and it works but I would like to know if there is a better way of constructing this formula without having to bring in all dimensions that might change from period over period.
4 replies
-
Hi
I don't understand. It IS showing in the same row as John Smith.
Can you explain further?
Thanks,
Ian
-
Hi
So what I would like to see is Prior Bank Balance Test and Bank Balance to show in the same row. For example, John Smith was the prior month officer and current month officer is Jane Doe. I would expect that John Smith do not show in the report at all as I only want to bring in prior month's bank balance. I would like to have the current officer name (which is Jane Doe in this case) and have Bank Balance and Prior Bank Balance for comparison next to Jane Doe
-
Hi
It appears in the John Smith row because that is the loan officer it is related to. Jane Doe doesn't have a prior period balance in the same way John Smith doesn't have a current period balance.
Assuming there is only ever one one loan officer per account for any one period, then you can retrieve the previous period balance for ALL loan officers (as the previous period will only have John Smith as the loan officer for account 111).
Raw data:
Prior Balance for specified period:
Hope that helps.
Ian