Hi all
I am trying to create a monthly transactional report which basically requires a new line for certain events in the month, which are as follows:
- Claim created
- Payment Authorised (& Amount)
- Reserve Updated (&Amount)
I only want to show a claim when one of the above events occurs, and I need a separate line for each event.
e.g. claim is created in a month - show a line of date
claim has a payment authorised in month - show a line of date
reserve value is updated - show a line of date
If more than one event happens on one claim in one month, I need a new line for each event
So if it is created and a payment is authorised in same month, then i need one for the claim created, and one for the authorised payment.
There are multiple columns of data in the report, which can be duplicated on each row, but i need the unique date and value (for amounts) on the corresponding rows.
The issue I am having is getting individual lines for each action.
I have tried multiple ways of doing it, but I did create filters to check the data as follows, which all work on their own as a calculated dimension in a chart table, but i get lines with claim created, and payments and reserves all in one line, the filters on their own produce multiple lines, but not across the multiple individual filters.
How can I combine all of these filters together into one expression?
The variables referred to below are the start and end of the reporting month.
Many thanks
Payment Filter:
=if((PaymentCreditApprovalCreatedDate >= vPreviousMonthStart and
PaymentCreditApprovalCreatedDate <= vPreviousMonthEnd) and
PaymentCreditStatusDescription = 'Approved'
,'Payment Credit Date: ' & [PaymentCreditApprovalCreatedDate] & ' ' & PaymentID,)
Reserve Filter:
=if(([Reserve Created Date] >= vPreviousMonthStart and
[Reserve Created Date] <= vPreviousMonthEnd)
,'Reserve Date: ' & [Reserve Created Date] & ' ' & ReserveID,
)
Claim Created Filter:
=if(([ClaimCreatedDate] >= vPreviousMonthStart and
[ClaimCreatedDate] <= vPreviousMonthEnd)
,'Claim Created: ' & [ClaimCreatedDate],)
.png "Former Employee"){kind=icon}
