Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
abhinav_rathore
Contributor III
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
sunny_talwar

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

View solution in original post

6 Replies
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
MVP
MVP

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

abhinav_rathore
Contributor III
Contributor III
Author

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

You made my day...

abhinav_rathore
Contributor III
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.

sunny_talwar

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

View solution in original post

abhinav_rathore
Contributor III
Contributor III
Author

Hey Sunny,

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

Thanks a lot brother