Hi All

[rajkumarb]

Please I need some help  in solving this issue

I have a sales table for last  three years with number of working days for each corresponding month and I  need to  calculate average sales per day with respect to the number of working day in each month

sales_table:

product - short form of product name

sale month

net sale -  for corresponding sale month

sale year

product name

working days - number of working days for corresponding month sale month

year month

I want to calculate average sales for each day in a month w.r.t.o number of working day in month in a pivot table in Dashboard using

Set Analysis

[francoiscave]

Hi Raj

The Networkdays() function would be useful to calculate the working days.

See the description and the link below:

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

http://community.qlik.com/message/407267#407267

Have fun with QV,

François

[rajkumarb]

Hi Francios

Thank  You

My Out Put in Pivot table should look like  this 

ex;  January average  = January net sale / January working days

       Feb average  = Feb net sale / Feb working days

      March average  = March net sale / March working days

Instead it is calculating wrongly as(actually my expression is wrong)

       January average  = January net sale / January working days

       Feb average  = Feb net sale / January working days

       March average  = March net sale / January working days

[#4]

Hi Raj,

Please find the below image. Its help ful for you.

Here Average Sales = Net Sales / Worikng Days. I'm applying this formula.

=Sum([Net Sale])/Sum([working days]) -- Expression for Average Sales

Please check this, I hope its help ful for you..

Thank you.

Sub2u

[rajkumarb · Accepted solution]

Hi Sub2a Red2y 

Thank You

I used the same expression but my Question is if month is Jan then it should lookup for Jan working days

I had some  mistakes done on date formats

the sales table date format is on 'yyyy-mm' and working days date format is ' mm' ' yyyy'  while  joining these two tables the expression

sum([sales])/ workingdays

didn't matched up but,  Now I changed the format of the date,  the  expression condition working, Thank you for guidance,   

[#6]

Hi Raj,

In previous file ur data showing in working days field is 24.0000, so

Can you attach the sample file .

so that we can help you in better way.

Regards,

Sub2u