Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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
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
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
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,
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