"Business week" weekly rollup question for watchlists of and excel reports

Turbo

Greetings, all:

I'm trying to get rollups to play better with weekly reports.

I've got a bunch of metapoints that run on times to calculate various things. These are all working fine.

However, I don't know how to get these to reliably output as "business week" style things in the report.

By "Business Week" I mean Last 7 days from Sunday night (So, say, Monday -> Sunday as a "business week"). We're outputting data that is compared to something else, and they use Business Week as Data. Mango's "week" is apparently DAY OF YEAR Modulus 7, which is fine, but provides difficulty for "business week" output.

Here's my report setup:
TIME PERIOD: Previous 7 days
Rollup Type: SUM of 7 DAYS
Configured Data:
Data calculated hourly

With that, when I run the report, I'm expecting the Time Stamp to be the START of the period or the END of the period. For example, when we do a "Previous 1 month" the timestamp stored in the excel spreadsheet is the 1st of the previous month.

However, with the time period / rollups I described above ,the timestamp stored in the excel spreadsheet tends to be like, the Friday in the middle of the period? I'm confused by this, and so are the customers.

So: Guess my questions are:
(1) How does Mango chose the timestamp when using Previous 7 Days time period and 7 day sum rollup, and
(2) Is there something I'm missing about how to get Mango to understand the concept of "business week" (Monday->Sunday) rather than modulus week (Day of Year % 7)?

This throws off our calculations a bit, since if you use a SUM on a month in the Watchlist viewer, you get fine data, and it all jives. But if you do SUM over a week, it looks odd, and insists on plotting values on Fridays rather than any other day. This presents a problem for us, since the data we're comparing to is done on Business Week (Monday -> Sunday) type basis.

Cheers,
-Greg

MattFox

(1) How does Mango chose the timestamp when using Previous 7 Days time period and 7 day sum rollup, and

(2) Is there something I'm missing about how to get Mango to understand the concept of "business week" (Monday->Sunday) rather than modulus week (Day of Year % 7)?

From what I've seen, they automatically use the base timestamp at the time of request and move around that point as an anchor. Same for watchlists, unless previously assigned, the date-time calendar will be set to the time on browser load.
If you want a "Business week" you're gonna have to define it.

Can you not use a Cron timer to "enforce" the time to schedule and fire the report? I'd do it at 12AM Monday. That would then work from Monday 12AM prior to Sunday 23:59:59. At least that's how I can see it working....
Perhaps that will provide the "Business week" format you are after.
EDIT:
mango's system claims:

Similarly, "Month(s)" starts the time span at midnight on the first day of the previous month and ends it on the last day of the previous month (when the number of periods is 1). Other period types work the same. A week starts on Monday at midnight in accordance with ISO standards

But given the issues you are having it does raise some eyebrows...
Trying to do it on the fly IMO will involve probably some slightly more involved datasource scripting....

Turbo

Thanks for the prompt response here- Something odd must be happening.

I've just been poking around again and here's what I've found:
(1) When in Watchlist, with a 1 week sum rollup type plotted over the "last 1 month" default setting, with a Line chart type, the data points displayed update on the Fridays (in this case, 4/23, 4/30, 5/7, 5/14, and 5/21) at midnight.

(2) When I run a report with the same settings, as seen below,

Mango says the same thing: I get the following timestamps out of Mango:

44309.00
44316.00
44323.00
44330.00
44337.00

Which, when converted to Date in Excel, are the same dates
04/23/21
04/30/21
05/07/21
05/14/21
05/21/21

As when we use the rollup as described before.

I'm also getting "previous month" data in the same way (1 month rollup sum), and that data outputs just fine: It timestamps as the START of the month, 4/1, and merrily outputs the proper sum for the month.

That's why I was investigating what Mango understands as a "week".

I just tried this on another Mango instance we run- And it did the same thing. Using the "week" rollup has end times starting on the FRIDAY of the week at Midnight.

Our systems are set to MDT time; I've gathered that it may make a difference for Java's internal time handling if we use the City-based time, rather than MST/MDT/etc (since midnight moves around).

Just now, to try this out, I set up the same report on out first machine to do "last 7 days", with the following settings:

With those settings, I would expect Previous 28 days and sum of 7 days, I would expect this to give me dates of Today (at midnight) or 7 days ago (at midnight). From right now (17:22 MST 5/26/21) that would give me 26, 19, 11, etc as the dates. Instead, I get this:

44309
44316
44323
44330
44337

Which excel turns into this
4/23/2021
4/30/2021
5/7/2021
5/14/2021
5/21/2021
Which is the same as when I used the "previous week" rollup type.

Anyways; The date shown by this instance is "Wed May 26, 2021, MDT"

Which means the clock isn't off by a few days, which would explain this.

Any thoughts on this?

MattFox

Looks buggy to me, or undesired behaviour at least. Have you tried forcing it to do it with a fixed timeframe as opposed to a relative?
If that works, perhaps we can leverage aascript or at least something with the api using dashboard components..

Fox

Turbo

Greetings, all:

Here's a new one for you.

I did an apt-get upgrade on our server and then restart Mango, and.. Now I'm getting dates that seem to make sense. I need to run this again tomorrow when I"m not so tired, but I think updating the server and rebooting mango.. Fixed this?

This is odd and slightly disconcerting, but yay for how software sometimes behaves.

I'll run some more reports, but I think resetting mango and apt-get updating our Linux install fixed this (for now anyways).

Thanks for your help..

Cheers,
-Greg