Network days using monthend and monthstart

[shonarach]

I need to find the average logged in a month to use in a calculation to see the average logged as at that day in the month, ie. the total is always automatically updating.

I have used the following code

Count([Reference])/(num(Monthend(max([BillingDateCreated])))- (Monthstart(min([BillingDateCreated]))))

This is working perfectly and is giving me the average per month and increases daily.

But i need to build in network days to find out how many item on average are logged during the working week.

Any help would be great. 

[swuehl · Accepted solution]

Maybe like

Count([Reference]) / Networkdays( Monthstart(min([BillingDateCreated])), Monthend(max([BillingDateCreated])) )

[shonarach]

Thank you so much.

I had tried your code but it tells me that I have an error in my expression.

I am following the syntax of NetworkDays(Start_Date, End_Date)

I have looked at the positioning of the brackets and they are fine. I have also tried to use Variables to set the Month Start Date and Month End Date and it still give me errors.

It looks like it should work as well

[swuehl]

Can't see anything obvious, too.

Can you tell me more about the context you are using this expression in? E.g. are you using this in a chart?

What are the dimensions / expressions of that chart?

And how are the fields Reference and BillingDateCreate related to each other in your data model?

I assume BillingDateCreated is interpreted as a date, so that you can get the max() and min() numeric values?

[shonarach]

I am using 2 dimensions in a straight table. Country_Overview and Target.

The target is an excel sheet that is updated weekly that is related to a country.

The count(Reference) is the amount of billing documents created. All billing documents have a billing date which is stored as a date function so max and min should work. All billing documents also belong to a country.

Would there be a way in set analysis?

[swuehl]

Set Analysis? Don't think that this will help us here, as far as I understood your request.

To find out why you get an error, create separate expressions for each part, like

=Count([Reference])

=Min([BillingDateCreated])

=Monthstart(Min([BillingDateCreated]))

etc.

Which part gives an error?

[shonarach]

I figured out my error.

I was missing one of the brackets after the function 

It is now working perfectly.

Thank you very much for the help on this, much appreciated!

[swuehl]

If your issue is resolved, then please close this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful