# Calculating lost followers metric

I am trying to calculate the lost followers and new followers per day for my company’s clients. I have "New followers" and "Profile followers" columns in Google Sheets but, in order to calculate "lost followers", I need to do ( [the previous day's followers + new followers] - current day's profile followers).

So, I am trying to get a column that shows "Previous day's followers".

There are two tables within one sheet.

Table 1 --ColA:ColC = table 1 with columns A=”Current Date”, B=”Name”, C=”Profile Followers”. The “Current Date”, and “Profile Followers” columns update daily.

Table 2 --ColO:ColR — table 2 with columns O=”Current Date”, P=”Name”, Q=”Previous Date”, R=”Previous Follower Count” To get the previous date, I just subtract the “Current Date” column - 1 in that column.

—the data values start in Row 3 for both tables

For the “Previous Follower Count”, I am trying to use QUERY to select the profile followers where the “Current Date” and “Name” from Table 1 matches the “Previous Date” and “Name” from Table 2.

QUERY FORMULA —problem: “Query completed with an empty output.”

`=QUERY( {\$A\$3:\$C,\$O\$3:\$R}, "SELECT Col3 WHERE Col1 = '"&\$Q3&"' and Col2 = '"&\$P3&"'", 0)`

• 1) "Table1": includes a "date" and "name" which suggests a daily record. Is it a record of ALL followers for each day or something else? Can a person's name appear on multiple rows for different dates (e.g. could "AB" appear on 1 Dec AND 2 Dec AND 3 DEC, etc?)? What does the "Profile Followers" Column signify as a part of a daily record? You said “Current Date” and “Profile Followers” columns update daily - do you mean that the "Name" column does not update daily? 2) "Table2": is this merely a copy of "Table1" that you are using for calculation purposes? Jul 23 at 1:24

You are trying to find the names of "Current" followers who were also followers on the Previous Day". You refer to this as a "Follower Count" though you don't actually count the names.

Your `query` doesn't work because it is trying to find an instance where the "Current date" equals the "Previous Date" ON THE SAME ROW. This never happens.

There are many ways to solve this problem. Consider the following:

Try this formula in cell R3:

• `=ARRAYFORMULA(IFERROR(VLOOKUP(query(ArrayFormula(({O3:O}-1)&{P3:P}),"select Col1 where Col1 <> '-1'"),{query(ArrayFormula({O3:O}&{P3:P}),"select Col1 where Col1 is not null"),query({P3:P},"select Col1 where Col1 is not null")},2,FALSE)))`

SAMPLE

LOGIC:

• ARRAYFORMULA/VLOOKUP
• Search key: similar to `=(O3-1)&P3` (as described in the question but expresses as a Query/Arrayformula).
• If the "Current date" = 2021-12-01
• and if "User name" = "_AB"
• then the value of the search key = "44530_AB"
• Range: an array consisting of:
• "Current date" & User name"
• similar to `=(O3)&P3`
• Using the values above, a result would = "44531_AB"
• "User name"
• similar to `=P3`
• Using the values above, a result would = "_AB"
• Index: 2 (the "User name")
• Is_sorted: False