Finding out the no of business days

[maahivee]

Hello Guys,

I have to take the sum(Sales) and multiply it by the current working day in the month say today is Aug 5th but working day number is 3. and then divide it by the no of working days in the month which is for Aug 2015 it is 21 days so my equations should be like (Sum(sales)*3)/21.

is there a way i could find out the current working day number and also the no of working days in the month.

[Clever_Anjos]

=Sum(sales) * networkdays(monthstart(Today()),Today()) /  networkdays(monthstart(Today()),monthend(Today()))

Where:

networkdays(monthstart(Today()),Today())  -> current working day number

networkdays(monthstart(Today()),monthend(Today())) -> no of working days in the month.

You can change Today() for your Date Field

[ogster1974]

function:networkday

You need to use the Networkday function. This should help you out.

Regards

Andy

[maahivee]

Hello Clever- Thanks for the solution, It is actually working but it is giving wrong numbers, meaning that i have 815,438.

i wanted to do (815,435/4)*21 so i am doing

=(Sum(sales) /  networkdays(monthstart(Today()),monthend(Today())))* networkdays(monthstart(Today()),Today())

the answer should be 4,281,285 where as it is giving 155,330.  Where do you think am i doing it wrong.

thanks in advance.

[Clever_Anjos · Accepted solution]

Maybe

=sum(Sales)  * (networkdays(monthstart(Today()),monthend(Today())) / networkdays(monthstart(Today()),Today())  )

[maahivee]

Perfect but can i know what is diff between both those expressions.

[Clever_Anjos]

It´s about math operators precedence.

* / are evaluated left to right with no precedence between then.

When I surround with "()" it forces QV (or Excel, etc) to evaluate one expression before other

[maahivee]

Hello Clever, I am trying to do a last year YTD sales expression and i am using the following expression

=SUM({<Year={'$(=(Max(Year)-1))'},Month=,Day=,Date={'<=$(=date(addyears(=day(max(Date)), -1), 'DD MMM YYYY'))'}}[Extended Price])

But the problem is it is giving full year but not YTD sales.

how to find out YTD last year. Thanks in advance.

[Clever_Anjos]

It would be simpler if you use flags to identify YTD,LYTD, MTD, and so on

Please read this blog post Qlik Design Blog and let me know if you have trouble into implementing it