Hi Folks, :)
I have the following scenario:
Case ID Activity Date Updated
11111 Bill Paid 09/25/2015
11111 Received Bill 08/01/2015
Now, on my report, I need to show the following for the same exact Case:
Case ID LAST Activity
Before Bill Paid Date Updated
11111 Received Bill 09/25/2015
So, Received Paid needs to show in the same Row showing the latest
information with the 09/25/2015 Date.
Not sure how to do this in the report or even Framework Model?
I tried MAXIMUM ([DATE]) but then I can't get the previous activity as Received Bill on the top row.
Please help if you can?
Thanks in advance for your time.
8) :) :)
Ok, in going after the previous row information, I can get the prev. date using the code below:
_add_days([DATE], -1) in FM Model.
But, now how do you get the rest of the previous row related to the Activity column which
Received Bill occurred before the "Bill PAID".
Thx in advance for your time.
hi ,
First create a Flag data item for bill paid and received Paid as 1 and 2.
then write a case statement for activity
case when (activity='bill paid' and sum(Flag)=3') then received paid
end.
Put a filter in report to show only received paid values.
Thanks,
TGS
Hi TGS,
Thank you for the reply!
I have never used a Flag Data Item? Is it a regular Data Item but giving it a value of some sort.
Also, the Activities could be many other Activities not just Received Bill. I take it I can just use
the specific Data Item like:
case when (activity='bill paid' and sum(Flag)=3') then [Activity]
end.
Thx so much for your time. :) :) :)