Display values on a month_dimension (instead of day)

[datanibbler]

Hi,

in most of my diagrams, I have two optional dimensions: Day (in which dase six data_points for Mon-Sat are displayed) and month (in which case twelve data_points are displayed).

For several diagrams so far I have just used the avg() fct. around the existing expression to show the values per month rather than per day - I have different expressions dependent on a variable which can be switched via a button).

<=> In my newest diagram, I have data from a database and I have a COUNT(DISTINCT ) as an expression.

=> When I try placing that inside an avg() fct like I have done so far, the editor says "Error in expression".

Can anybody tell me what's wrong with that and, more importantly, what I could do instead to the same effect?

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi Marcus,

the one for last week works fine. I've already implemented it - the status_light works, too.

Now I have to do the same for the past month. There I cannot use that Pers_Nr5&Weekday since there would be duplicates. So do I have to calculate one more field in my master_calendar, a Monthday (1-31)?

I'll try.

P.S.: Okay, that works so far - but then I'll have to calculate a field in my master_calendar to hold the nr. of working_days (Mo-Fri) in a given month. I haven't yet figured out how to do that since I cannot use set_expressions in the script.

[marcus_sommer]

You could simply set to each weekday (mo-fr and without holidays) a 1 as value and then make a sum(WeekDayCounter).

- Marcus

[datanibbler]

Hi Marcus,

I have that now, so I know by what to divide.

(Btw.: That difference - 5 vs. 7 weekdays - is due to our database, I checked. The emps_who_are _sick are only tracked from Mon to Fri).

Now I'm going to have a bite to eat and then I'll go on working on this.

[datanibbler]

Hi Marcus,

this is also done. I have now calculated the nr. of working_days (Mo-Fri) and I have repeated the same formula (just adapted) and that is that.

I'll just implement that into the live_copy of this app (as a historical value) and then comes the next challenge: A YTD average.

Doing that the same way would be a tad more difficult - would mean calculating the days of the year past so far and the nr. of working_days past so far...

P.S.: I'm still having trouble with the latter. It cannot be that hard, I think I remember having done it before, but I just cannot figure it out now.

Can you help me there once more, please?

Phew - that was long in the making. Finally I have constructed a set_expression to calculate the nr. of weekdays YTD. Now I guess I can put together just about the same formula for the YTD_value.

[datanibbler]

All done.

Many thanks for all!