Need Help to calculate "Business Day - Roll to Monday"

[Anonymous]

Hi Folks,

I need one help in a scenario where I need to find Business Day - Roll to Monday in Qlikview.

Please find the attached excel for reference which contains data for current month i.e. December 2017.

WEEKEND_OR_HOLIDAY column contains Yes and No values, as the column name itself specifies - If Calendar_Date is a working day then it will show us as 'No' else 'Yes'.

I need to calculate 'Roll to Monday' column which contains numeric values and I am not getting logic for it in Qlikview.

Below is the explanation of requirement-

* For Calendar_Date = 1, the correspondent value will be always 1 in 'Roll to Monday' column.

* Here Next Working Day is 4th December so 'Roll to Monday' column value should come as 2.

* For weekends(2nd December-Saturday and 3rd December-Sunday) or Holidays date, the value should come as same which has assigned for the next working date( Here 4th December ) i.e. 2 and so on...

Please refer to the attached excel for more clarity and feel free to ask me if more clarification is needed.

Any help would be appreciated.

Thanks,

Abhinav

[sunny_talwar]

Does your date field only contain day info? 1, 2, 3... etc... how do you know if it is weekday or weekend? Do you have a field like WEEKEND_OR_HOLIDAY?

[swuehl]

A script statement similar to your excel expression could look like

LOAD CALENDER_DATE,

     WEEKEND_OR_HOLIDAY,

     [Roll to Monday],

     If( Peek('ROLL2') and Peek('WEEKEND_OR_HOLIDAY')='YES',Peek('ROLL2'),Rangesum(Peek('ROLL2'),1)) as ROLL2

FROM

[Roll To Monday.xlsx]

(ooxml, embedded labels, table is [Export Worksheet]);

[Anonymous]

Thank you so much Brother for the quick and accurate solution.

You made my day...

[Anonymous]

Hey Stefan,

Can you calculate the same logic in Qlikview Expression ? I am facing some issues when trying to implement this in back-end script.

[sunny_talwar · Accepted solution]

Check my response here

Re: Need Help to calculate "Business Day - Roll to Monday" in QV Expression‌

=RangeSum(Above(TOTAL If(If(Len(Trim(Above(TOTAL WEEKEND_OR_HOLIDAY))) = 0, 'NO', Above(TOTAL WEEKEND_OR_HOLIDAY))  = 'YES', 0, 1), 0, RowNo(TOTAL)))

[Anonymous]

Hey Sunny,

Tussi Great ho Sir, awesome and perfect logic which I was looking for.

Thanks a lot brother