Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm a basic Qlikview user and am struggling to build a dashboard within the requirements due to the below issue.
I have followed the guide posted by hicCanonical Date on Canonical dates however I am unable to find a way to make it work with my data.
I think it is due to being unable to get a table with a fine enough grain.
Any help or advise would be greatly appreciated.
I have two tables. a policy table and a claims table.
Basic Example.....
A policy can have many rows (as shown in the below tables), and a policy can also have many claims (example below).
Policy Table:
Policy Number | Transaction Date | Inception Date | Cancellation Date | Transaction Type |
Pol_1 | 15/01/2016 | 01/02/2016 | - | New Business |
Pol_1 | 03/02/2016 | 01/02/2016 | - | Adjustment |
Pol_1 | 04/03/2016 | 01/02/2016 | - | Adjustment |
Pol_1 | 05/03/2016 | 01/02/2016 | 05/03/2016 | Cancelled |
Claims Table:
Policy Number | Claims Number | Claim Opened Date | Claim Settled Date | Claim Rejected Date |
Pol_1 | Matt1 | 14/02/2016 | 21/02/2016 | - |
Pol_1 | Matt6 | 16/02/2016 | 17/02/2016 | - |
Pol_1 | Matt12 | 22/02/2016 | - | 04/03/2016 |
Attached is my current table view, but I am getting duplicate data from the Canonical Calendar
Thanks in advance
Gary
Hi Gary, your expressions can be fixed yo work with that model, ie, for claims opened:
Count({<DateType={'Opened'}>} If(MonthYear=ClaimOpenedMonthYear, MatterID))
Edit: and about the transacted... maybe adding a DISTINCT clause... Or an aggr between Policynumber and transaction date... I don't really know how to count 2 different transactions made by one policy in one day.
Hi Gary
I don't understand why you have a canonical calendar associated to the link table but then also have three separate calendars for transaction, policy and cancellation. Can you provide this segment of your script please so I can better understand your issue.
Regards
Brian
Can you post a small qlikview document that demonstrates the actual problem?
Hi Gysbert,
I have attached a QVW, the dashboard is very basic.
On the selected example (Unique PolicyNumber =PET/W/50056068/PLUS 03/01/2016) I should have:
Only one transaction count in in Jan-2016 (not on Fab-16)
One count in Jan-2016 for a claim (MatterID 50056143) & one Count for March-2016 (MatterID 50060146)
I should point out that not all policys will have a claim or a cancellation.
Thanks
Gary
Hi Gary, from what I see you're right, you need more granularity to follow that guide, some field that is unique in each row like a PolicyTransactionId... I guess (I don't know how the policy_canonical field is created) that now is doing:
If you select Month=january and [date type]=transaction, the DateLink table will say: 'ok, I have one of those: the policy Pol_1', so it goes to the PolicyLoadTable and returns all records with Pol_1 as policy_canonical.
Hi Gary, your expressions can be fixed yo work with that model, ie, for claims opened:
Count({<DateType={'Opened'}>} If(MonthYear=ClaimOpenedMonthYear, MatterID))
Edit: and about the transacted... maybe adding a DISTINCT clause... Or an aggr between Policynumber and transaction date... I don't really know how to count 2 different transactions made by one policy in one day.