topic Re: Cumulative open days and count in QlikView

Cumulative open days and count

Sun, 14 Sep 2014 08:07:55 GMT

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

Re: Cumulative open days and count

Sun, 14 Sep 2014 09:40:43 GMT

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

Re: Cumulative open days and count

Sun, 14 Sep 2014 09:54:36 GMT

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

Re: Cumulative open days and count

Sun, 14 Sep 2014 10:09:40 GMT

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

Re: Cumulative open days and count

Sun, 14 Sep 2014 13:51:40 GMT

Hi Mahesh,

Here is the iterated Notification data for last 17 periods.

Hope this is what you are looking for.

Regards,

Kalpesh