I'm trying to build a line chart that shows the date B:B for the X Axis and D:D value for Y Axis, but only where A:A = invoice.closed.
I've been trying a variety of things, but the numbers aren't adding up right. Any suggestions on how to build this query in Klipfolio?
Hello Pat,
Greetings!
Your formula should look something like this:
For x-Axis:
GROUP(SLICE(B:B))
- And in the properties tab, set the data format to Date/Time and input format: yyyyMMdd and output format: MMM dd, yyyy
- SLICE is used to remove the header row.
- GROUP is used to remove duplicates.
For invoice.opened Series:
LOOKUP(!x-axis, GROUP(SELECT(B:B, A:A="invoice.opened")), GROUPBY(SELECT(B:B, A:A="invoice.opened"), SELECT(D:D, A:A="invoice.opened")))
* Where !x-axis is a reference to x-axis (This will evaluate the same formula as x-axis)
- LOOKUP is used to align the data rightly across the x-axis dates.
- GROUPBY is used for aggregation. Default method is: SUM.
References:
SLICE: https://support.klipfolio.com/hc/en-us/articles/216182837-SLICE-video-?query=Slice
GROUP & GROUPBY: https://support.klipfolio.com/hc/en-us/articles/215547998-GROUP-GROUPBY-and-COUNTDISTINCT-video-?query=group
SELECT: https://support.klipfolio.com/hc/en-us/articles/216182567-SELECT-video-?query=
LOOKUP: https://support.klipfolio.com/hc/en-us/articles/216182777-LOOKUP-video-
If you face any issues in building this formula in your klip, please write to us at: support@klipfolio.com
We will help resolve the issue.
Have a good one!
~Kavya Mallur
This is perfect. Awesome stuff and quick response!
Thank you!