Seperating a month dimension that accepts 2 fields by those fields

Tue, 07 Oct 2014 16:01:46 GMT

Hey Everyone,

So I have a data set of IT tickets that has one column labeled OpenDate and one labeled CloseDate. I want to create a bar chart that has two dimensions a bottom level of month and then for each month 2 columns one that shows how many tickets were opened and how many were closed. I think I am on the right track with my month dimension its a huge nested if statement that accepts both the OpenDate and the CloseDate, it look something like this:

IF(Month(CloseDate)=1, 'January',IF(Month(OpenDate)=1, January, IF(Month(CloseDate)=2, 'February'...

And so on for all of the months.

My problem then arises with the next dimension. I can create a comparison of the amount of tickets closed vs left open is I use the created field below

IF(Month(ClosedDate), 'Closed', 'Still Open')

However what I would really like are 2 total counts one of the total number of tickets closed and one of the total opened for that month. So far I have been unable to add a third dimension so it seems as though what ever must be done must be done in this created dimension. However, I'm open to any suggestions.

Thanks,

Christian

Re: Seperating a month dimension that accepts 2 fields by those fields

Anonymous — Tue, 07 Oct 2014 16:21:34 GMT

Have a look at the IntervalMatch() function.

This blog IntervalMatch by Henric is good.

Re: Seperating a month dimension that accepts 2 fields by those fields

Gysbert_Wassenaar — Tue, 07 Oct 2014 16:22:52 GMT

See my suggestion in this discussion: One table, Two Date Fields

Re: Seperating a month dimension that accepts 2 fields by those fields

Tue, 07 Oct 2014 20:46:50 GMT

Thanks Gysbert you are some kind of wizard.

topic Re: Seperating a month dimension that accepts 2 fields by those fields in QlikView

Seperating a month dimension that accepts 2 fields by those fields

Re: Seperating a month dimension that accepts 2 fields by those fields

Re: Seperating a month dimension that accepts 2 fields by those fields

Re: Seperating a month dimension that accepts 2 fields by those fields