Cumulative open days and count

[#1]

Hi ,

please help me with this

Imagine that Notification No 1 was 2 days open end of Aug, and  Notification No  2 was 1 days open.

Sum is 3 days open Aug.

2 open Notification

Imagine that Notification No 1 was closed in Sep, Notification No 2 is still open end of Sep, but there was 1 new Notification created middle of the month.

End of Sep  the situation is:

Notification No 1: not be taken into consideration anymore

Notification No 2: 31 days open

Notification No 3: 15 days open

Cumulative open end of Sep  is 46 days.

2 open notification

Similar if completion date is not present it should be considered as open 

Notification No	Created on Date	Completion by Date New	Total No of Days Open	Sales Divition
1	8/28/2013	9/30/2013	33	10
2	8/30/2013	10/30/2013	61	10
3	9/15/2013	11/30/2013	76	10
4	10/29/2013	10/30/2013	1	20
5	12/31/2013	2/28/2014	257	20
6	1/29/2014		228	20
7	1/31/2014	2/28/2014	226	10
8	2/28/2014		198	20

[#2]

Hi Mahesh,

The solution needs you to take snapshot of the situation at end of month. In the attached example, end of month is taken as last day of previous month.

Also, where completion date is not present, it is replace with Today's date (you can choose last date of the period in consideration)

Regards,

Kalpesh

[#3]

Hi Kalpesh,

Thank you, But in I need historical data like,This will give me  only current open status,

                    Aug 2013 Sep 2013 .... till current

open days       3              46

Count              2              2

[#4]

There are two ways to achieve this.

1. Have snapshot data stored in database(if available) at end of each period.

2. In Qlikview, Iterate the Notification table to generate data snapshot at end of each period.

  If you want that the output for a given period in time remains as is (i.e. we freeze the snapshot), you may store data for each period in qvds like Notification_201301.qvd , Notification_201302.qvd so on.

Finally, during current period, we load them all in the document (already calculated) and perform calculations just for current period.

Regards,

Kalpesh

[#5]

Hi Mahesh,

Here is the iterated Notification data for last 17 periods.

Hope this is what you are looking for.

Regards,

Kalpesh