-1

For each row in Column C, i'm trying to computer the number of likes divided by the number of posts. For example in C2 cell from day one i divide 5 likes by 1 post, from day 2 i divide 7 likes by 1 post, then in day 3 i divide a sum of (8+1) likes by 2 (total number of posts). As you can see in day 4, the two posts gain some likes, so im trying to compute same from the day before ...like (10+6) likes divide by 2 (total posts).

enter image description here

How do i write my query formula like this based on the sheet below? This is my output of query formula. I already count the unique post id numbers. I was trying to filter and computer data from another sheet :

=QUERY(QUERY(IMPORTRANGE("sheet", "Social media posts!A:AS"),"SELECT Col1, Col14, Col12, Col10, Col23, Col16, Col13, Col37, Col2, Col3  WHERE Col2='instagram'", 1),"SELECT  Col1, Col10, COUNT(Col10),SUM(Col2),SUM(Col3), SUM(Col4), SUM(Col5), SUM(Col6), SUM(Col7) group by Col1, Col10")

In the image below, in each post id from Column B, I already counted number of their unique post id #s by each date. Because in the query formula, i group the selections by date in column A. Each of the post id in 8/24/2020 already counted total of 1 in Column C. In column D, I already sum the number of Likes by the number of unique post ids (Column C).

For another one column, in day one 8/24/2020, I want to sum all the values in Column D divided by total # of post id. In day 2 ,08/25/2020, I want to sum all the unique post id's values in Col D including the values from day 1 (8/24 + 8/25) divided by total number of all unique post ids from (8/24 + 8/25)

So what's the correct query formula for this?

enter image description here

Jcbyte
  • 207
  • 1
  • 4
  • 11
  • It seems that your tags include `google-apps-script`. In your question, you also expect the solution using Google Apps Script? – Tanaike Oct 10 '20 at 00:51
  • I would like to see Google Apps script solution too . I'm very curious to know what's the script look like. – Jcbyte Oct 10 '20 at 00:54
  • Questions in this site should be specfic ( in other words, choose, formula or script, bear in mind that you could post a second question for the other) – Rubén Oct 10 '20 at 00:55
  • Thank you for replying. I could understand about the tags. But unfortunately, I cannot understand about the logic for retrieving your goal from your bottom image. This is due to my poor understanding. I apologize for this. When I could correctly understand about it, I would like to think of the solution. – Tanaike Oct 10 '20 at 00:58
  • 1
    @Rubén Sounds good. I'll post a second question. Thanks. – Jcbyte Oct 10 '20 at 00:58
  • @Tanaike In the image below, in each post id from Column B, I already counted number of their unique post id #s by each date. Because in the query formula, i group the selections by date in column A. Each of the post id in 8/24/2020 already counted total of 1 in Column C. In column D, I already sum the number of Likes by the number of unique post ids (Column C). – Jcbyte Oct 10 '20 at 02:05
  • For another one column, in day one 8/24/2020, I want to sum all the values in Column D divided by total # of post id. In day 2 ,08/25/2020, I want to sum all the unique post id's values in Col D including the values from day 1 (8/24 + 8/25) divided by total number of all unique post ids from (8/24 + 8/25) – Jcbyte Oct 10 '20 at 02:06
  • Thank you for replying and adding the explanation. But I have to apologize for my poor English skill. Unfortunately, from your additional information, I cannot still understand about the logic for achieving your goal. I deeply apologize for my poor understanding. Although I cannot still think of the solution of your question, I noticed that an answer has already been posted. I think that it will resolve your issue. – Tanaike Oct 10 '20 at 03:16

1 Answers1

1

To have unique dates (Col1 from your outer QUERY) you have you remove the post id column Col10 from the outer QUERY)

Rubén
  • 34,714
  • 9
  • 70
  • 166