Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Maybe like
Count([Reference]) / Networkdays( Monthstart(min([BillingDateCreated])), Monthend(max([BillingDateCreated])) )
Maybe like
Count([Reference]) / Networkdays( Monthstart(min([BillingDateCreated])), Monthend(max([BillingDateCreated])) )
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
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?
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?
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?
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!
If your issue is resolved, then please close this thread.