Net change of facebook followers in 30 day period

user05889

I am trying to make a calculated field which determines the net change in facebook followers in the last 30 days. The data has columns: 

example

Date: November 04, 2018 1:00:00 AM

Total Page Fans: 262

 

How can I determine the number of fans 30 days ago, and yesterday and output the difference for my card? I am very amateur at this logic so please try to be descriptive!

Unknown

Hi, @user05889,

 

Welcome to Domo! Hope you're learning new things and having success with the product.

 

The good news is you can absolutely do this, even being an "amateur". Beast Mode calculations provide the flexibility to perform these calculations and many others

 

You probably already know that you can create custom "Beast Mode" calculations in the card analyzer. To do this, enter analyzer mode of your card and click the "ADD CALCUALTED FIELD" button at the bottom left. This will open the window where you can define a new custom calculation.

 

Your data looks something like this:

Date       | Fans  
-----------|-------
2019-01-06 | 350   
2019-01-05 | 348   
2018-12-07 | 320   
2018-12-06 | 319   

But you want it to look like this:

Date       | Fans  | Compare Date | Date Label
-----------|-------|--------------|-------------------
2019-01-06 | 350   | 2019-01-06   | Current 30 Days
2019-01-05 | 348   | 2019-01-05   | Current 30 Days
2018-12-07 | 320   | 2019-01-06   | Prior 30 Days
2018-12-06 | 319   | 2019-01-05   | Prior 30 Days

When you create you visualization, you'll use "Compare Date" as the X axis, "Fans" as the Y axis, and "Date Label" as the series.

 

The idea here is that you want to compare one day to a day 30 days before. To accomplish this, you'll create a new calculated field called "Compare Date". This new field contains the actual date if the actual date is within the last 30 days; and it contains the actual date + 30 days if the actual date is within the 30 days prior.

 

You're essentially asking this simple question: Does the date of the record fall within the last 30 days? If yes, then use the date of the record as the "Compare Date." If not, then does the date of the record fall within the 30 days before that? If yes, then add 30 days to the actual date of the record and use that as the "Compare Date". This logic is implemented using a case statement. A case statement looks like this:

CASE
  WHEN [some condition or list of conditions is true] THEN [some result]
  WHEN [some other condition is true] THEN [some other result]
  ELSE [yet another result if none of the earlier conditions are true]
  END

 

The various "when" portions of the statement are evaluated in order. Once the computer finds a "WHEN" that is true, it stops checking the other "WHEN" statements and returns the "THEN" value. If none of the "WHEN" statements are true, then it returns the "ELSE" value. If you haven't specified a value for "ELSE", then the case statement returns NULL. You can have as many "WHEN" statements as you need to express your business logic.

 

The formula for the "Date Label" calculation will be very similar. In that case, rather than outputting the date of the record or the date + 30 days, you want to output 'Current 30 Days' or 'Prior 30 Days' or 'More than 60 days ago' for other dates (here is where you would use the "ELSE")

 

You'll need to use some combination of the the following functions in you calculations: 

• DATE_ADD()
• DATEDIFF()
• CURRENT_DATE()

 

You can learn how these functions work by searching for them in the list of functions within the Beast Mode window and reading how they operate in the Function Description.

 

I hope that gets you moving in the right direction.

 

Best,

Dan

 

ST_Superman

I tried to step through this logic in an ETL for you.  I think that some of this is more complicated in ETL vs MySQL, but here goes.

 

 

There are four main steps.

 

Step 1: Assign a value for the date you want to compare to.  In your case, you mentioned 30 days prior and 1 day prior.  In ETL I used the "Date Operations Tile" to create two new fields, Date-30 and Date-1.

 

Step 2:  You need to join these dates to your original data set and then remove the duplicate (Date_1) field.

 

 

Step 3: Lookup the number of fans from 30 days ago and join that to your data.

 

Step 4:  Repeat step 3, but for one day ago.

 

Finish the ETL by attaching an output data set tile and naming your new data set.  

 

This will give you a data set with the following schema:

 

 

Good luck to you.  Let me know if you have questions.