Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shuang923
Contributor
Contributor

Calculating the average of number occur per workdays.

Hello,

 

I need some help if any can help me out how to write the formula here.

So i have a simple 3 columns excel to be import into Qlik Sense.

My column 1 is the Month/Date/Year

My column 2 is sum of count for each Month/Date/Year.

I want my column 3 to do the following.

The average count of column 2 based on workdays. For example, on 6/1/2019, i have total sum of 1078 counts. In June 2019 - there is 20 working days. So it would be 1078/20 = 53.9

Then for 5/1/2019, i have 1242 counts. Working month for 5/1/2019 would be 22 workdays = 1242/22 = 56.4545

So each month will calculate what their workdays are divided by the sum of count on column 2.

How do i do this?

 

 

Capture.JPG

1 Reply
jonathandienst
Partner - Champion III
Partner - Champion III

NetWorkDays() - for example:

=Sum(<count field>) / NetWorkDays(MonthStart(<date field>), MontheEnd(<date field>))

(replace the placeholders with the correct field names)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein