How to hide transfers from Quick Insights

On the Insights sheet of the foundation spreadsheet there’s a section called “Tiller Quick Insights”. The second insight there is “Your largest outflow in the last 90 days was ‘X’ for ‘Y’ on ‘Z’”.

While my transfers seem to be hidden from the rest of the sheet (i.e., Top 10 outflows) they’re not hidden from the Quick Insights.

Is this a bug or intended?

In either case, how do I hide transfers from that field? It’s not terribly useful when it shows credit card payments.

1 Like

In case it is helpful, and someone here will know what is happening (I’m a newbie), but my Insights | Top Outflows does NOT show Transfers. (I assume somehow you are categorizing as Transfers amounts other than payments and the usual transfer type accounts.)

Yeah, I am categorizing them as transfers. The source of the issue may be that my Apple Card account is an unlinked account that uses the Tiller Labs “Import CSV Line Items” tool.

To be clear, it’s not in the Top 10 Outflows table. It’s in the prose version in the dashboard up top.

I was hoping that since I used the “Apple Card” import in that tool it might add the account to the Accounts sheet. My guess is that because it’s not there and can’t be marked as hidden on that sheet it doesn’t filter from the Insights sheet.

Anyone know if there’s a way to add an account to the Accounts sheet that’s imported via CSV? That way I can determine if it’s the source of the problem.

I’d post a screenshot if this app would let me, but it doesn’t seem to ¯_(ツ)_/¯

It looks like the query that selects the largest outflow doesn’t filter at all, honestly:

=iferror(query(indirect(P15),"SELECT "&P10&", "&P13&", "&P12&" WHERE "&P10&" >= date '"&TEXT( today()-P6,"yyyy-mm-dd")&"' ORDER BY "&P12&" ASC LIMIT 1",-1))

I’m hesitant to edit a core sheet (particularly this one) because I’m unsure how overwrites happen with new versions.

Feel free to rework the formulas as you like, @lightcap.

Two notes:

  1. If you break anything, you can restore to the original using the Tiller Labs add-on.
  2. Be aware (as you note) that your changes will be lost if you later chose to upgrade or restore any of your “managed sheets”.

Good luck,
Randy

Cool. Makes sense.

But, shouldn’t this behavior be considered a bug? It’s a transfer and thus should be hidden, especially if it’s hidden in everything else.

Hi @lightcap and @Larry,
Thanks for alerting us about this. You are correct that the text display at the top of Quick Insights does not filter out Transfers.

In general, Tiller filters transactions from most reports if the Category of the transaction has been designated as a “Hide” category, using the “Hide From Reports” column in the Categories sheet.

The current formula used for that Quick Insights textual data doesn’t filter out these Hidden category transactions. But I’m considering a fix so those type of Transactions would be filtered out. I’d prefer this method of filtering, rather than filtering out the Transfer transactions, since some users might want to see the Transfer transactions.

If you set whatever Category you are using for these Transfer transactions to “Hide” in the Category sheet “Hide From Reports” column, then they would not appear as an option in the textual data in my proposed fix.

Would that solution work for you?

If you want to test it, put this in cell O49 and make your transfer Category hidden:

=iferror(query(indirect(P15),"SELECT "&P10&", "&P13&", "&P12&" WHERE "&P10&" >= date '"&TEXT( today()-P6,"yyyy-mm-dd")&"' "&if(len(P45),"AND NOT "&P45,"")&" ORDER BY "&P12&" ASC LIMIT 1",-1))

Jon

For what it’s worth I came across this same bug and fixed it with:

=iferror(query(indirect(P15),“SELECT “&P10&”, “&P13&”, “&P12&” WHERE NOT(”&P45&“) AND “&P10&” >= date '”&TEXT( today()-P6,“yyyy-mm-dd”)&“’ ORDER BY “&P12&” ASC LIMIT 1”,-1)) in O49 (and similar in O51)

This seems similiar to what @jono suggested and seems to work well.

I’m curious how something like this works itself upstream so that I don’t have to maintain a different Insights tab. I’m new to Tiller, and not sure how updates trickle down or how often that happens. I’m guessing when/if they do they’ll clobber my change.

Hi @josh.m.sharpe ,
If i’m understanding your comment correctly, I don’t believe any other sheets would pull data from the Quick Insights sheet, so I change there shouldn’t trickle to other places.

You would only lose your customized formula if you updated the Quick Insights sheet.

These comments are mostly based on my recollection. It is possible there’s a sheet i’m not aware of or i’m forgetting that could use Quick Insight data.

Jon

I think you misunderstood - probably my fault. Words are hard.

I’m pointing out that your fix (and my similar fix) both seems like good contributions to the Insights tab.

I’m curious how that type of contribution can be worked upstream so that others can benefit AND so that I don’t have to keep ‘re-fixing’ it any time I update the Insights tab from Tiller.

I assume that Tiller updates these tabs periodically? I’m new to Tiller, so I’m not sure at what frequency that happens. From what I’ve seen if I had to guess it doesn’t happen automatically, but only when I manually refresh the tab. All the same, manually or automatically refreshing would clobber this fix and it’d be nice if it was incorporated upstream.

1 Like

Hi @josh.m.sharpe ,
Now I understand your comment.

In general, when there is a bug or problem with a sheet, the template gets updated. There is no set frequency. Sometimes a sheet will update with added functionality as well.

I think in this case, the thinking was some people would want to see these transfer transactions. I realize this is inconsistent with other sheets. I’ll flag this to my Tiller contact and see if it’s worth changing the master template here.

Jon

Well, to be clear this isn’t specific to transfers. It’s specific to hidden categories. In my case, (and I think OPs) transfer just happens to be hidden.

Thanks!

The intent with the Insights sheet is that when you first fill your Foundation Template you’re not going to have any data categorized so we don’t really know what is a transfer at that point and nothing would be “hidden” so everything gets included, and you can hopefully gather some “quick insights” immediately without having to do anything. How useful they are is up for debate, BUT they may pique curiosity to take the next steps of categorizing transactions to get more useful insights.

So I think the formula would need to factor in whether the transaction is categorized or not, and NOT just if it’s using a hidden category. My assessment based on what’s described here is that it’s not factoring that in at all. If it’s not categorized, we’d want to show it (as that is the state when someone first fills their sheet). It if is categorized it should not show transactions where the category is hidden and possibly not also show transfers.

To your question @josh.m.sharpe about updates and communicating this type of thing, bugs/issues should be reported directly to our support team via chat on the Console at https://sheets.tillerhq.com/auth/login

We do our best to correct bugs as quickly as possible. However, our product team prioritizes bugs, issues, and requests based on the severity of the issue and how many customers are impacted or requesting this feature as well as the team’s current existing backlog of issues and upcoming planned feature releases.

If we push a template update, you can update using the Tiller Money Feeds add-on to get the latest changes and use the “archive existing” option to not blow out any customizations you’ve made. Generally for small bugs we don’t make announcements about those unless they’re rolled into a larger product release. Larger releases will get an announcement here in the community.

1 Like