topic Avg in labor days and weekend days in QlikView

Avg in labor days and weekend days

Wed, 24 Feb 2010 12:08:55 GMT

Hello all,

I have a variable v_Date7days.

I have a pivot table with the following dimension and want to display only the 31 days less than my variable v_Date7days --> this is not my problem!

I also have an expression:

sum(Qty)

The next expression is where I have a problem.

As you know within the dimension range we have labor and weekend days. I need to have an expression that calculate an average only for labor days and another expression for the weekend days.

I have a field that give me that information (Val_Labor_Days=1 and Val_Weekend_Days=1)

Does anyone have any idea how to solve this?

Thanks in advance.

João

=if(Date>v_Date7days-31 and Date<=v_Date7days, date(Date))

Avg in labor days and weekend days

Miguel_Angel_Baeyens — Wed, 24 Feb 2010 12:17:58 GMT

Hello Joao,

You already have networkdays() function that returns the number of working days between two dates, something like

networkdays('01/01/2010', '31/01/2010')

You may use this function instead.

Avg in labor days and weekend days

Wed, 24 Feb 2010 12:22:11 GMT

Hi,

If you have a Field named Val_Labor_days containg 1, if it is a labor day an Null otherwise.

And you want to do an avg on the labor days.

Can't you simply do a "avg(Qty * Val_Labor_days)"

Or using set analysis "avg({$<Val_Labor_days={"1"}>} Qty)"

Hans

Avg in labor days and weekend days

Wed, 24 Feb 2010 12:23:09 GMT

But in the date range I also could have holidays (Carnival -2010/02/16) and is considered in the group of weekend/holidays.

I need to:

sum(Qty) for the labor days / count(distinct Date) --> but only for the ones indicated in the dimension

Avg in labor days and weekend days

Wed, 24 Feb 2010 12:28:48 GMT

Hello Hans,

if I use the set analysis the result it shows is always "1".

It doesn't work

Best rgs

João

Avg in labor days and weekend days

Wed, 24 Feb 2010 13:09:48 GMT

Hello again,

I'm trying the following expression:

count({$<Date {">$(date(v_Date7days)-31)"}, Date {"<=$(date(v_Date7dias))"}, Val_Labor_Day= {1}>} total distinct Date) --> count Labor Days

and it gaves me 26 for all the days (the total number of days in 2010).

I need to have the avg of labor days (should be sum(total column(2)/number Labor days (21).

The same for weekend days (10).

I will have only 2 distinct avg. One for labor (181 695) and other for weekends (69 242)

Date	Total Qty	Qty Labor Days	Qty Weekend days	count Labor Days
09-01-2010	86.176	0	86.176	26
10-01-2010	48.960	0	48.960	26
11-01-2010	186.241	186.241	0	26
12-01-2010	170.967	170.967	0	26
13-01-2010	169.148	169.148	0	26
14-01-2010	182.650	182.650	0	26
15-01-2010	181.725	181.725	0	26
16-01-2010	78.588	0	78.588	26
17-01-2010	48.740	0	48.740	26
18-01-2010	178.204	178.204	0	26
19-01-2010	185.717	185.717	0	26
20-01-2010	185.940	185.940	0	26
21-01-2010	183.327	183.327	0	26
22-01-2010	189.221	189.221	0	26
23-01-2010	89.206	0	89.206	26
24-01-2010	56.581	0	56.581	26
25-01-2010	183.948	183.948	0	26
26-01-2010	182.581	182.581	0	26
27-01-2010	183.790	183.790	0	26
28-01-2010	182.536	182.536	0	26
29-01-2010	185.079	185.079	0	26
30-01-2010	88.463	0	88.463	26
31-01-2010	55.602	0	55.602	26
01-02-2010	181.412	181.412	0	26
02-02-2010	194.009	194.009	0	26
03-02-2010	182.418	182.418	0	26
04-02-2010	175.350	175.350	0	26
05-02-2010	178.354	178.354	0	26
06-02-2010	83.292	0	83.292	26
07-02-2010	56.807	0	56.807	26
08-02-2010	172.982	172.982	0	26

I really need your help.

Bst rgs~

João