Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
Please bear with me as i'm relatively new to QS
I have a table of data i.e with dates claims were opened and dates they were closed.
I'd like to summarise on a pivot with
column heading being month year (which i know how to do)
Row 1 = total claims registered in the month - using our registration date
Row 2 = Total claims closed in that month using closed date example below
this is what i sort of want
Jan 21 Feb 21 Mar 21
Registered 1 2 4
Closed 0 2 3
Underlying data
claim no. Reg Date Closed Date
1234 Jan 21 Feb 21
4321 Feb 21 Feb 21
7788 Feb 21 Mar 21
9922 Mar 21
8888 Mar 21 Mar 21
7777 Mar 21
6666 Mar 21 Mar 21
is this possible please
Hi KWHITEHURST,
try loading this table twice. One time only with the dates where claims were opened and the second time where claims were closed. Likes this:
Claims:
Load
claim_no,
Reg_Date as date,
'Registered' as Event
From .....
Concatenate Load
claim_no,
Closed_Date as date,
'Closed' as Event
From....
Then use your field 'Event' for rows, field 'date' for columns and Count(Event) as Measure. I didn't test this but i guess this should work fine.
Let me know if it worked.
Regards,
Can