4

I have the following table

enter image description here

I need a graph with dates on X axis (with the option to group by weeks/months/years), values on Y axis and lines for each name (A,B and C) representing the sum of values on the given date.

For example A is 4 on 01/01; 3 on 01/02 and 0 on 01/03...

Something like this enter image description here

1
  • 1
    How many characters are there? Just 3?(A,B,C) Are you ok with scripts?
    – TheMaster
    Mar 5, 2018 at 16:08

1 Answer 1

1

One approach is to separate the data into three sets using three query functions such as

=query(A:C, "select A, sum(C) where B contains 'A' group by A", 1)

But this has a drawback: the dates for which 'A' has no data are omitted instead of having 0. A modified approach is to preprocess the values with arrayformula. For example, in D2:

=arrayformula(if(iserror(find("A", B2:B)), 0, C2:C))

which outputs the content of C if "A" is found in column B, and 0 otherwise. Similarly in E2 and F2 for two other names. Finally, the query

=query(A:F, "select A, sum(D), sum(E), sum(F) where A is not null group by A", 1)

will group the values in D, E, F by date, with this output:

+-----+---+---+---+
| 1/1 | 4 | 9 | 8 |
| 1/2 | 3 | 1 | 3 |
| 1/3 | 0 | 2 | 2 |
+-----+---+---+---+

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.