Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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..

   

IDDateTarget Value
11-Feb-18150Monday
22-Feb-18145Tuesday
33-Feb-18140Wednesday
44-Feb-18135Thursday
55-Feb-18130Friday
66-Feb-18130Saturday
77-Feb-18130Sunday
88-Feb-18125Monday
99-Feb-18120Tuesday
1010-Feb-18115Wednesday
1111-Feb-18110Thursday
1212-Feb-18105Friday
1313-Feb-18105Saturday
1414-Feb-18105Sunday
1515-Feb-18100Monday
1616-Feb-1895Tuesday
1717-Feb-1890Wednesday
1818-Feb-1885Thursday
1919-Feb-1880Friday
2020-Feb-1880Saturday
2121-Feb-1880Sunday
2222-Feb-1875Monday
2323-Feb-1870Tuesday
2424-Feb-1865Wednesday
2525-Feb-1860Thursday
2626-Feb-1855Friday
2727-Feb-1855Saturday
2828-Feb-1855Sunday

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

1 Reply
dwforest
Specialist II
Specialist II

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