Help creating reporting dashboard

1

Hi all,

I’m trying to create a stat reporting dashboard but have hit a couple of snags.

I have two tables - one for individual stat instances, the other for stat types. The former looks up from the latter. I’d like to set up a frequency so that a stat is reported once every x days or months. So, let’s say I want someone to collect the number of newsletter subscribers we have once a month.

To do this, I’ve had the stat type info have columns for a) person responsible for stat collection b) desired frequency c) the date the stat was most recently collected. My problem is in b) and c). For b), the duration column only allows for days, making it difficult to make it a monthly requirement. For c), I am struggling with the formula to get the latest date within the other table that matches the stat type I’m looking for.

Would greatly appreciate any help!

Here’s what I mocked up so far, including my hilariously incorrect formula for determining c: Reporting Dashboard

2

Hi @Tim_Richardson1 :blush:

I took a look at your doc and added 2 columns (to not disturb your original set up) :blush:.

So, the Recent Input is, I think what you were trying to do in your Most recent input field.
Well, at least, it looks for the most recent dates your stat type has been used in your table Stat Inputs using this formula :blush: :

[Stat Inputs].Filter([Stat Type].Type = thisRow.Type).[Date Input].Max()

Now, for the next monthly input, I’ve added a column Next Input Date and, using the newly created Recent Input and the function RelativeDate(), I added a month to that date :blush: with this formula :

thisRow.[Recent Input].RelativeDate(1)

Regarding the general idea of frequency though (as you might want/need different frequency for different stat types), I’ve added a small table called Frequency linked to your Stat Types table where you can select the frequency.
The simple reason to create that table is that it’s easier to adapt if and when needed.

The adapted formula after that change looks like this :

SwitchIf(
  thisRow.Freq = Monthly,
  thisRow.[Recent Input].RelativeDate(thisRow.Freq.Month),
  thisRow.Freq = Weekly,
  thisRow.[Recent Input] + Days(thisRow.Freq.Days)
)

What this SwitchIf() does is :
See if the frequency in Freq is set on Monthly, then it will add the number of months (set up in the Frequency table) to the Recent Input Date using RelativeDate() and if the frequency is Weekly it will add the number of days (set up, also in the Frequency table for this frequency) to the Recent Input Date :blush: .

This means that if you need an “Every 6 months” frequency, all you would need to do is add an Every 6 months row in the Frequency table with 6 in the Month column and modify accordingly the SwitchIf() of your Next Input column in your Stat Types table :blush: and so on…

I hope this helps :blush: !