Calendar, working days and sales

[#1]

Hi all,

I should create a report that tells how sales is doing at the moment.

The expression is something like this. Let's assume here that todays date is January 15 th

= sum ( actual sales (this months sales, year to date..., result is 10000)

/ Number of done working days  today(from same fact table, result is 15)

* Number of working days left in the month (calculated from fact table, result is 21-5=16)

+ actual sales (10000)

--> Explanation: First actual sales (till today) is calculated and then divided with done working days to get daily average. This average is then multiplyed with days left to know what is the estimated sales per month. Of course after that actual sales is added as well.

So, here we are calculating estimated sales for current month. Same expressoin numbers is like this = (10000/15*16).

The problem is to calculate number of days. In fact table there is date and column, where it is told whether day is a work day or not.

Help, please... 😉

[nilesh_gangurde]

Hi,

you can use the NetworkDays() function.

networkdays (start:date, end_date {, holiday})

Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps.

Examples:

networkdays ('2007-02-19', '2007-03-01') returns 9

networkdays ('2006-12-18', '2006-12-31', '2006-12-25', '2006-12-26') returns 8

OR

if you want to customise the holidays then please have a look.

YA(H)Q Blog: working days

-Nilesh

[MK_QSL]

You want this for finding Monthly Sales or Yearly Sales?

[#4]

Monthly. Monthly estimation is the target here.

[MK_QSL]

=SUM({<InvoiceYear = {'$(=Year(Today()))'}>}Sales)

+

(SUM({<InvoiceYear = {'$(=Year(Today()))'}>}Sales))*(NetWorkDays(Today(),MonthEnd(Today())-1))/NetWorkDays(YearStart(Today()),Today())

This will give you expected Sales until Monthend strating from 1st Jan 2014 ..

[MK_QSL]

=SUM({<InvoiceMonth = {'$(=Month(Today()))'}>}Sales)

+

(SUM({<InvoiceMonth = {'$(=Month(Today()))'}>}Sales))*(NetWorkDays(Today(),MonthEnd(Today())-1))/NetWorkDays(MonthStart(Today()),Today())

This will give you only data for current month

[MK_QSL]

Little Update on Monthly Data

=SUM({<InvoiceMonth = {'$(=Month(Today()))'}, InvoiceYear = {'$(=Year(Today()))'}>}Sales)

+

(SUM({<InvoiceMonth = {'$(=Month(Today()))',InvoiceYear = {'$(=Year(Today()))'}}>}Sales))*(NetWorkDays(Today(),MonthEnd(Today())-1))/NetWorkDays(MonthStart(Today()),Today())

[MK_QSL]

The NetworkDays will exclude Saturday and Sundays.

If you want to exclude Public Holidays... you need to use..

NetWorkDays(MonthStart(Today()),Today(), PublicHolidays) and

NetWorkDays(Today(),MonthEnd(Today())-1, PublicHolidays)

in case of more than one public holidays.... you need to create a variable which contains all public holidays...

Please check below link for further...

Find Net Working Days

[#9]

Hi,

Working days are already in data in every row (1/0). Could I use that instead?

[#10]

There are several rows per one day...