Summarising by Dates

[KWHITEHURST]

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

[canerkan]

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