topic Re: Hi All in QlikView

Hi All

rajkumarb — Wed, 24 Dec 2014 09:55:07 GMT

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

Re: Hi All

francoiscave — Wed, 24 Dec 2014 09:56:37 GMT

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

Re: Hi All

rajkumarb — Wed, 24 Dec 2014 10:17:39 GMT

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

Re: Hi All

Thu, 25 Dec 2014 04:59:30 GMT

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

Re: Hi All

rajkumarb — Mon, 29 Dec 2014 15:18:59 GMT

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,

Re: Hi All

Mon, 29 Dec 2014 16:08:29 GMT

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