How to make a dashboard display values per calendar week?

Well I had to try it out and with very basic HTML skills I came up with:

The numbers below each month are room numbers. I repurposed a hotel reservation DB.

Here is the code:

let getData := (select Reservations);
let monthList := sort(unique(getData.month_calc));
let content := "
<style>
.right{
text-align: right;
}
</style>
<div style=overflow-x:auto;>
    <table>
        <thead>
            <tr>
                <th>Month</th>
                <th>Nights</th
            </tr>
        </thead>
        <tbody>" +
    for loop1 in monthList do
        let x := getData[month_calc = loop1];
        "<tr>
                <td>" +
        first(x.month_name) +
        "</td>
                <td class=""right"">" +
        sum(x.Nights) +
        "</td>
        </tr>" +
        for loop2 in unique(x.room) do
            "<tr>
            <td>" +
            loop2 +
            "</td>
            <td class=""right"">" +
            sum(x[room = loop2].Nights) +
            "</td>
        </tr>"
        end +
        ""
    end +
    "</tbody>
    </table>
</div>
";
html(content)

Can I say there needs to be a better editor. Trying to keep track of the open/closing of html to Ninox code is mind breaking.

This is in a table called Dashboard. To prep for all this I created a formula field called month_calc in Reservations that uses the month() function to get me month number of the date field. I also created a field called month_name that uses the monthname() function, so I have the proper month name to display

As you can see in line 1 I do a select of the Reservation table. Then in line 2 I get a unique list of months from line 1.

Line 3 starts the mind breaking HTML code.

Lines 4 - 8 is the CSS style section.

Line 18 is where we start getting data to display. Here we start our first for loop where we take the unique list of months from line 2.

Line 19 creates a new variable that filters line 1 to only contain records that have the month number in line 18.

Line 22 gets the first instance of the month_name field so it shows the correct month name.

Line 25 then sums the field Nights from line 19.

Line 28 then starts another for loop that uses a list of unique room numbers reserved in the month found in line 18.

Line 34 sums the field Nights again, but first we had to filter line 19 to only find records that have the room we are looking at from line 28.

I'm sure others who are better CSS coders can make it more pretty.

Filip Serica said:
Is there maybe an easier way with some pivot tables?

 How do you use Ninox? app, public or private cloud?

If Public cloud then you need a Professional license for Pivot tables.

I don’t know if the app gives you pivot tables. I’m not at my Mac to test it out.