I just added a pie chart based on the newly posted (9/4/2020) info Heather posted in the Tiller HQ Money page.
Is there a way to query the data that would be similar to the drop down menu found in the Insight sheet period dropdown box?
Tracking the top 10 categories being dependant on a specific month is great but for me it would be much more useful if I could query by the eight choices as featured in the sheet located on Insight tab in the foundation sheet that would reflect the data in a pie chart.
Thanks in advance!
Link to pie chart post here:
This is a great idea @mkarbowsky. I don’t know off hand, but probably pretty easy to pull off… @jono or @randy - is it easy to leverage the pre-built time periods from the dropdown in the Transaction Analyzer on the Insights sheet?
Hi @mkarbowsky,
It is possible to use those pre-built in time periods, but it would take a number of steps to modify the sheet.
@heather 's Pie Chart post takes advantage of the time period filtering in the Monthly Budget sheet. So you need to create the date range and then filter Categories in the Transaction sheet by the new range.
You could take advantage of the existing Insights sheet.
Copy the cells in I15 to L15 and I16 to L16 into your Pie Chart sheet.
You need to also set the Data Validation on the Period selector cell to match the Insights sheet.
Note that you also need to include a Conditional formatting rule for row 16 which Hides the Custom Date Range, unless Custom is selected as the Period.
The format rule should apply to the new Custom Date Range section with a Custom formula of: =if($J$15=“Custom”,false,true) , where J15 is where you select the Period. Your sheet might use a different cell so you would need to adjust the formula to match.
The formatting style for this rule is White text and White background, so it disappears.
Then, copy in the formulas for the Start and End Date in column O and P somewhere in your sheet.
The start date formula is:
=ifs(J15="Custom",J16,J15="This Year",date(year(today()),1,1),J15="Last Year",date(year(today())-1,1,1),J15="Last 12 Months",eomonth(today(),-12)+1,J15="Last Month",eomonth(today(),-2)+1,J15="This Month",eomonth(today(),-1)+1,J15="This Quarter",date(year(today()), QUOTIENT(month(today())-1,3)*3+1,1),J15="Last Quarter",date(year(today()), QUOTIENT(month(today())-1,3)*3-2,1))
The end date formula is:
=ifs(J15="Custom",L16,J15="This Year",date(year(today())+1,1,1)-1,J15="Last Year",date(year(today()),1,1)-1,or(J15="Last 12 Months",J15="This Month"),eomonth(today(),0),J15="Last Month",eomonth(today(),-1),OR(J15="This Quarter",J15="Last Quarter"),eomonth(P3,2))
You might need to adjust the J15 and J16 references to match your sheet.
Now you need to write a query in A1 to replace your existing one that refers to the Monthly Budget sheet. For example:
=QUERY(Transactions!B:E,"SELECT D,SUM(E) WHERE D IS NOT NULL AND B >= date '"&TEXT(H1,"yyyy-mm-dd")&"' and B <= date '"&TEXT(H2,"yyyy-mm-dd")&"' GROUP BY D ORDER BY SUM(E)")
This assumes you are using the standard Transactions sheet where column B = Date, D = Category and E = Amount. Also, the start date range is in H1 and then end date range is in H2.
Let us know if that works for you.
Jon
1 Like