Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
in all of my apps I have one chart with shows the presence_quota, based on figures from our personell_database.
- There is a binary field (0 or 1) for every employee who is associated with a certain area;
- By just summing up that field, I get the correct nr. of employees every day.
<=> The problem is when I change the dimension from "day" to "month" (a standard feature of all my charts) and thus I need to
aggregate the data.
- Currently I have the following formula:
>>> aggr(sum({$<%Datum = {"$(= '<' & DATE(Today()))"}>}Anz_MA_soll), Monat)) <<<
(I don't want this data for "Today" because other data is not yet available, so it would not match and result in incorrect figures)
That, however, gives me gigantic figures. I just tried out dividing it by 28 - roughly the nr. of days in a month - and that results in plausible figures.
The issue - and the question - is, how can I build that into the expression since every month has a different nr. of days (and, even more so, working_days)
Thanks a lot!
Best regards,
DataNibbler
I mean, in the expression which i had suggested, use the Max(Distinct Day) instead of Sum.
Regards,
Kaushik Solanki
HI,
You can create an inline table with months and days, just like below.
Load * inline [
Month, Days
Jan, 31
Feb, 28
March, 31
......
];
Now link this table with your Month Fields in the data model.
In chart if you have the month as dimension, you can directly divide it by days.
Sum(Monat)/Sum(Distinct Days)
Regards,
Kaushik Solanki
Hi Kaushik,
I have a master_calendar with two fields which could be of use here:
- "Monatstag" (day_of_month) - that goes up from 1 to whatever, it is 7 for today
- "Monatstage" (days_in_month) - that is 31 for today
Could I use those? - keeping in mind that the aggregated figure for August is already on display today, but dividing by 31 would result in minuscule figures, of course...
Thanks a lot!
Best regards,
DataNibbler
Hi,
Then you will have to use the max instead of sum.
Regards,
Kaushik Solanki
Hi Kaushik,
I just realized that is not quite going to work - the field "Monatstag" is a static field, not a function - so that only works with one day selected. Well, I can calculate it, it's just for the current month.
What do you mean with using max instead of sum?
Best regards,
Moritz 😉
I mean, in the expression which i had suggested, use the Max(Distinct Day) instead of Sum.
Regards,
Kaushik Solanki
I don't understand - never mind. I made it now, I think - for all the past months, the field from my master_calendar is good. The challenge is only the current month - and there I can use
>>> (num(Today() - Monthstart(Today()))+1) <<<
That will give me a 7 for today, for example, and so the figure is plausible - I'd say it is correct, namely 35, about the same that I have in the other view, with the dimension "day".
I will close the thread.
Thanks a lot!
Best regards,
DataNibbler