Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rajkumarb
Creator II
Creator II

Hi All

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


1 Solution

Accepted Solutions
rajkumarb
Creator II
Creator II
Author

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,   

View solution in original post

5 Replies
francoiscave
Partner
Partner

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
Creator II
Creator II
Author

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

Not applicable

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
Creator II
Creator II
Author

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,   

View solution in original post

Not applicable

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