topic Re: Network days using monthend and monthstart in QlikView

Network days using monthend and monthstart

shonarach — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Network days using monthend and monthstart

swuehl — Mon, 25 Apr 2016 15:36:36 GMT

Maybe like

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

Re: Network days using monthend and monthstart

shonarach — Mon, 25 Apr 2016 16:03:29 GMT

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

Re: Network days using monthend and monthstart

swuehl — Mon, 25 Apr 2016 16:55:30 GMT

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?

Re: Network days using monthend and monthstart

shonarach — Tue, 26 Apr 2016 09:07:51 GMT

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?

Re: Network days using monthend and monthstart

swuehl — Tue, 26 Apr 2016 16:12:38 GMT

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?

Re: Network days using monthend and monthstart

shonarach — Wed, 27 Apr 2016 16:31:26 GMT

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!

Re: Network days using monthend and monthstart

swuehl — Wed, 27 Apr 2016 16:34:44 GMT

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

Qlik Community Tip: Marking Replies as Correct or Helpful