0

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)

Thanks in advance!

3
  • 1
    Hi, welcome to WebApps. Your question should include ALL the information required to understand your scenario; it should NOT require that a voluneer must open your spreadsheet to see more information. In addition, your question doesn't describe/explain the information contained in your spreadsheet, your definitions of a "new follower", "profile follower", or "previous day followers", or in which cells/sheets totals are required. Also, your spreadsheet has only one sheet (not two) and none named per your question. Please edit your question to provide complete details for your scenario.
    – Tedinoz
    Jul 20 at 20:48
  • Thanks for editing your question. It looks like you decided not to include a spreadsheet-in my comments above I wasn't suggesting "no spreadsheet" but rather that the question should contain all the relevant information that will be found in the spreadsheet. A spreadsheet can be valuable because it provides real data, enables volunteers to relate the information in your question to the spreadsheet., and also see how/where a successful outcome should appear. I'd encourage you to supply a link to a spreadsheet - provided that the spreadsheet reflects the information described in the question.
    – Tedinoz
    Jul 23 at 0:43
  • 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?
    – Tedinoz
    Jul 23 at 1:24

1 Answer 1

0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.