topic Re: Open items for perticular month in QlikView

Open items for perticular month

Tue, 26 Aug 2014 14:25:31 GMT

Any one have any other way to archive this? by doing this in way suggested by Rupas , the link to fact table will be based to created date but in this has to be linked to ID

I trying calculate count of total open items at end of the month based on completed date below table gives the example of what i am trying to get as output. Can someone please help me on this.

ID	Created date	Completed date
1	1/30/2014	1/31/2014
2	1/31/2014	2/10/2014	Open in Jan
3	2/1/2014	4/10/2014	open in Feb&March
4	2/15/2014	3/2/2014	open in Feb
5	3/10/2014	3/10/2014
6	3/12/2014	4/2/2014	open in March
7	3/30/2014		open in March&April
8	4/3/2014		Open in April
9	4/3/2014	4/3/2014	Open in April
10	4/5/2014		Open in April
			Required Output
			Jan-14	Feb-14	Mar-14	Apr-14
	Open		1	2	3	3

Re: Open items for perticular month

alematex — Tue, 26 Aug 2014 14:36:55 GMT

why id 3 was open in feb and march?

Re: Open items for perticular month

Tue, 26 Aug 2014 14:41:07 GMT

Id 3 was completed or closed in April hence it should be counted has open for both the months.

Re: Open items for perticular month

Anonymous — Tue, 26 Aug 2014 15:04:27 GMT

Hi,

Try like this

=num(count({<(Completed date={$(=MAX([Completed date]))},WeekKey=,FinancialYear=,FiscalQuarter=,([Completed date]={'Open'},WKey=>}OPen_Issues),'#,##0;(#,##0)')

Re: Open items for perticular month

Tue, 26 Aug 2014 16:20:27 GMT

Check this out Mahesh

testMahesh.qvw