Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
ajmayers
New Contributor

Transactional Report Issues

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],)

 

1 Reply
Support
Support

Re: Transactional Report Issues

AJ, best advice I can give you on this one is to attach your QVW with some data, so folks can see the data model and stuff, not sure you are going to get much otherwise, as this one is fairly complex.  My post will kick it up, but not sure anyone is going to be able to do much.  The only thing I have otherwise is the base URL to the Design Blog area, hundreds of how to posts out in that area, you can dig around to see if you can find something to help there, but otherwise I am no help to you, sorry.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.