How to calculate the column considering the previous value and carry forward the value to the weekends

Anonymous — Tue, 20 Feb 2018 09:44:09 GMT

Hi ,

I have a requirement which is as follows

I have taken sample data .There are 2 columns ID and Date. I need to plot a graph to calculate the # of incidents which has accomplished the target .Here i need to calculate the target column.

Target column should be calculated something like below.

Say its the 1st of Feb month ,we have # of incidents defined as 150 . we have the target coming from different qvd for feb month.we perform some calculations and arrive that per each day we need to complete 5 incidents.

so i need to get the below result

ID Date Target
1 1 feb 2018 150

For 2nd Feb it must be 150 - 5 (Incident that needs to be closed per day)= 145. For 3rd feb it must be 145 - 5 =140 .

One more condition here is assuming 1st feb as Monday ,We need to carry forward the target value which we calculate on friday to monday,i.e on sat and sun the target value must be carried forward..

ID	Date	Target Value
1	1-Feb-18	150	Monday
2	2-Feb-18	145	Tuesday
3	3-Feb-18	140	Wednesday
4	4-Feb-18	135	Thursday
5	5-Feb-18	130	Friday
6	6-Feb-18	130	Saturday
7	7-Feb-18	130	Sunday
8	8-Feb-18	125	Monday
9	9-Feb-18	120	Tuesday
10	10-Feb-18	115	Wednesday
11	11-Feb-18	110	Thursday
12	12-Feb-18	105	Friday
13	13-Feb-18	105	Saturday
14	14-Feb-18	105	Sunday
15	15-Feb-18	100	Monday
16	16-Feb-18	95	Tuesday
17	17-Feb-18	90	Wednesday
18	18-Feb-18	85	Thursday
19	19-Feb-18	80	Friday
20	20-Feb-18	80	Saturday
21	21-Feb-18	80	Sunday
22	22-Feb-18	75	Monday
23	23-Feb-18	70	Tuesday
24	24-Feb-18	65	Wednesday
25	25-Feb-18	60	Thursday
26	26-Feb-18	55	Friday
27	27-Feb-18	55	Saturday
28	28-Feb-18	55	Sunday

How do I calculate the target value column month wise and carry forward the value which is on Friday to the weekend also.

Re: How to calculate the column considering the previous value and carry forward the value to the weekends

dwforest — Wed, 21 Feb 2018 00:16:52 GMT

I would tackle this in the load script using Previous and Weekday..

So data would need to be sorted by date, then a field could be scripted:

Previous(TargetValue) - If(Weekday(date)<=4,5) As TargetValue

You would need to add a seed like, where date was smaller that the first date in your data.

LOAD * INLINE[

ID, Date, TargetValue

0, 1/1/2018, 100];

Or if the 1st of the month is a set point,

If(Day(Date)=1, 100, Previous(TargetValue) - If(Weekday(date)<=4,5)) As TargetValue

topic Re: How to calculate the column considering the previous value and carry forward the value to the weekends in QlikView

How to calculate the column considering the previous value and carry forward the value to the weekends

Re: How to calculate the column considering the previous value and carry forward the value to the weekends