Help With Dates (Picker + Chart)

Ask for Help
1

I have a performance tab, which displays the # of views a post has received Today, the Last 7 Days and the Last 30 Days in a Chart Element based on a Choice Picker.

Column 1 is Date Viewed.

Column 2, 3 and 4 are each Single Value Columns (Today, 7th Day, 30th Day).

Column 5 is an IF THEN ELSE Column which says: If Date Viewed is on or after TODAY, THEN Today. If Date Viewed is on or after 7 Days, THEN 7 Days. If Date Viewed is on or after 30 Days, THEN 30 Days.

Column 6 is the Choice Picker Column, which is User Specific.

Column 7 is a Single Value Column that uses the Choice Picker Date.

Column 8 is another IF THEN ELSE Column which says if Column 5 = Column 7, then True.

My Chart then shows all TRUE values, which is like 75% accurate.

The issue I am having is that Last 30 days excludes all posts that are also included in Last 7 days and Today because they have their respective Labels.

Ex: Post was viewed 5 times today, 15 times in the Last 7 Days and 40 times in the Last 30 Days.

What it currently displays: 5 views Today, 10 views in Last 7 Days and 25 views in the Last 30 Days.

How would I display:
Last 30 Days = Last 30 Days (25) + Last 7 Days (10) + Today (5) = 40
Last 7 Days = Last 7 Days (10) + Today (5) = 15
Today = Today (5) = 5

2

Here is an example of the chart in my app:

image
image1170×2532 288 KB
image
image1170×2532 257 KB
image
image1170×2532 298 KB

The 7 Day excludes Today. And the 30 Day excludes both the 7 Day and Today.

I’d like to have 7 day include Today’s numbers and 30 Day include both the 7 Day and Today’s numbers.

3

Because you’re labeling each type as only one, while one row would fit 2 or 3 labels. How are you populating the chart? I guess not from a relation, just by filtering?

4

I have a Post Performance Sheet that populates the chart. It’s filtered based on the IfPerformance Column is True. Maybe I have to change what makes it True…

image
image2224×1668 399 KB
image
image2224×1668 277 KB
Screen Shot 2021-03-25 at 7.54.55 PM
Screen Shot 2021-03-25 at 7.54.55 PM712×1096 40.2 KB

5

I think you need to split that into 3 columns. Just off the top of my head, here is how I would approach it…

  • 3 separate if-then-else columns, one for each of today, 7 days, 30 days. Each column returns either true or blank.
  • a template column with the single word true
  • 3 multi-relation columns, each joining the template column to one of the if-then-else columns
  • 3 rollup columns to get the respective counts, via the multi-relations

Haven’t tested that, but I’ve used a similar approach in other situations, so fairly confident it would work.

1 Like
6

Actually, just thinking about that a bit more - you’d probably need 3 math columns to plug into those if-then-else columns. Each math column would calculate now-1, now-7, now-30, respectively.

1 Like
7

Awesome, thank you I will try that tomorrow and let you know!

8

I just figured it out! So my Picker creates a Single Value Column called SinglePerformance.

I added 3 IF THEN ELSE Columns:

#1. IF Date is Today, THEN Today
#2. IF Date is Last 7 Days, THEN Last 7 Days + IF Date is Today, THEN Last 7 Days
#3. IF Date is Last 30 Days, THEN Last 30 Days + IF Date is Last 7 Days, THEN Last 30 Days + IF Date is Today, THEN Last 30 Days

I then added a 4th IF THEN ELSE Column:

IF SinglePerformance is #1, THEN True + IF SinglePerformance is #2, THEN True + IF SinglePerformance is #3, THEN True.

I then Filtered my graph based on the 4th Column if TRUE.

1 Like