QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for
Did you mean:
Contributor III

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

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

1 Solution

Accepted Solutions
MVP

Check my response here

=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)))

6 Replies
MVP

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?

MVP

A script statement similar to your excel expression could look like

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]);

Contributor III
Author

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

Contributor III
Author

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.

MVP

Check my response here

=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)))

Contributor III
Author

Hey Sunny,

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

Thanks a lot brother