Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
erickd1190
Contributor III
Contributor III

Sum by total number of work days

Hello everyone,

I need to sum by the total number of workdays from beginning of data set to end.

To do so I tried to use this formula but cannot nest the expression.

I know that you can use AGGR to nest expressions but I have been unable to make it work.

Any thoughts?

1.)   SUM(DailyPT)*(MAX(NETWORKDAYS(MIN(visitdate),TODAY())-MIN(NETWORKDAYS(visitdate,TODAY()))))

2.)   SUM(DailyPT)*(MAX(AGGR((NETWORKDAYS(MIN(visitdate),TODAY()))-MIN(AGGR(NETWORKDAYS(visitdate,TODAY())))))



Update*

Just realized that I cannot use NetworkDays because that function is counting the days, not finding.




To summarize I need to sum by the number of work days inbetween the MIN(visitdate) and MAX(visitdate)



1 Solution

Accepted Solutions
ahaahaaha
Master
Master

Hi Erick.

May be like this (look attached file)

Regards,

Andrey

View solution in original post

6 Replies
Gysbert_Wassenaar

Just realized that I cannot use NetworkDays because that function is counting the days, not finding.

Not finding? What should it be finding?

Can you give an example with some numbers and the result you expect?


talk is cheap, supply exceeds demand
ahaahaaha
Master
Master

Hi Erick.

May be like this (look attached file)

Regards,

Andrey

View solution in original post

vinieme12
Champion II
Champion II

during load create a new flag field to mark working days, and use the Flag to only include working days

Sum({<WorkDayFlag = {1}>}DailyPT)

nbrusova
Contributor II
Contributor II

This just counts weeks from a month ago date and doubles it:

interval(today(1)-AddMonths(today(1),-1))-(week(today(1))-week(AddMonths(today(1),-1))-1)*2

Might require some extra conditions depending on weekday(visitdate)

erickd1190
Contributor III
Contributor III
Author

Hi Vineeth,

Seems this works best when the Daily PT is calculated into a Monthly sum first.

Any advice on how best to link the daily target, by position, to the calendar?

erickd1190
Contributor III
Contributor III
Author

I have an example for you here:

I need to Multiply the DailyPT by the number of workdays from the beginning of the dataset (Based on date selections)

To me the expression is like this:

SUM(DailyPT) * NumberofWorkDays