Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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
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
Hi Mahesh,
Here is the iterated Notification data for last 17 periods.
Hope this is what you are looking for.
Regards,
Kalpesh