Open cases balance at month end

[tinkerz1]

Can anyone point me in the right direction here.

I have records with a case reference, 'Open date of case' and 'Close date of case'.

Not all cases will be closed at month end, so all Null fields will be the open cases to be worked on.

I would like to build a historical table from the first month a case was open to the last date a case was open, the balance by month.

first is their a qlik function to do this.

OR

Do I have to make a new table, replacing the NULL data with the current date.

Then iterate through each month since open and generate a new record if it was open on in that month by checking the closed date of case against the month end date?

Just checking my process.

Thanks,

Neil.

[sunny_talwar]

I think you will have to replace null with current date because otherwise QlikView won't know this. But you can do this in a new field and don't have to touch your raw data which might be helpful in other ways. Once you do that, you can potential interval match this with your master calendar table to get to what you are looking for. or you can use While Loop to create dates between your start date and newly created end date

[Anonymous]

Would it be this?

[tinkerz1]

Ok,

This while loop works, is there a function for date difference in months, so I can calculate the right formula for the iter?

Complaints_Open_Balances:
Load DISTINCT
CaseIDR5,
AddMonths(LoggedDate, IterNo())  as ReferenceDate2,
IterNo() AS REFITR
Resident Complaints_Fact
While IterNo() <= DATE(ResolvedDate_FULL_DATA_SET,'DD/MM/YYYY') - AddMonths(LoggedDate, IterNo()) ;

[sunny_talwar]

Check Rob's response here:

How Do I Get the Number of Months Between Two Dates?

[sunny_talwar]

May be like this:

=(Year(ResolvedDate_FULL_DATA_SET)*12) + Month(ResolvedDate_FULL_DATA_SET) -

(Year(AddMonths(LoggedDate, IterNo()))*12) + Month(AddMonths(LoggedDate, IterNo()))