Need help with Total with Conditional Parameters

1

Hi there - I am calculating a table based on the leads that have already been invoiced and paid and leads that have been invoiced but unpaid.

I created this table below and used formula ([Master View of Team Events - No Filter].Filter(Year([Event Date])=Year(thisRow.Month) AND Month([Event Date])=Month(thisRow.Month)).Total.sum()) to get the total $$ invoiced to the customers and now I cannot figure out how I can filter out the ones already paid.

image
image919Ă—449 53.4 KB

So, the way I want it to be filtered are the leads with the label, “Paid” as the invoice status.

-
-678Ă—547 50.5 KB

How can I create the formula based on this? Any input would mean a lot! Thank you! :slight_smile:

2

hi @Renee_O ,

Since there is no sample doc I can only help you with the first part of the formula.

You may want to try this one.

thisTable.Filter(
[Event Date].Year().Contains(thisRow.Year) and
[Event Date].Month().Contains(thisRow.Month))

It will output the rows that fit the filters and from there you can continue your set up.

I hope it helps, christiaan

3

Hi Christian, thank you! I will try this and let you know!

4

HI Christian,

It does not work for me. I can explain it better. So I will need to get the sum of the leads with two conditions, “If it is paid” and if it is paid, the formula should calculate the total.

image
image766Ă—492 54.7 KB

Let me know if you need more info so I can send more info. Thanks for your help!

[Master View of Team Events - No Filter].Filter(Year([Event Date])=Year(thisRow.Month) AND Month([Event Date])=Month(thisRow.Month)).Total.SumIf().[Invoice Status]="Paid")
5

Hi @Renee_O ,

I am sorry, but without a shared doc (everybody can edit setting) I am unable to help you further.

cheers, christiaan

1 Like
6

HI Christian,

Here you go: https://coda.io/d/_dzc8Ra384tG/Team-Events_suwSV

Let me know if you need anything further.

7

hi @Renee_O ,

thanks for sharing the file, that is the way forward.

you figured out a few things, I rewrote this formula:

[DB Events].Filter(Year(Month)=Year(thisRow.Month) AND Month(Month)=Month(thisRow.Month)).Count()

Into this one to have an easier to read and understand formula.

[DB Events].Filter(
[Event Date].Year().Contains(thisRow.Month.Year()) and
[Event Date].Month().Contains(thisRow.Month.Month())).Count()

with this in mind we can filter on other items as well I showed in the doc.

[DB Events].Filter(
[Event Date].Year().Contains(thisRow.Month.Year()) and
[Event Date].Month().Contains(thisRow.Month.Month()) and
invoiceStatus.Contains(Paid)).totals.Sum()

based on this input you see how easily you can add elements in your filter and thus figure out the open questions. Give it a try and if you get stuck, please reach out again.

Cheers, Christiaan

1 Like
8

Hi Christian,

I tried the formulas you made. It worked! Thank you so much

9

I am glad you worked it out based on the examples. Proper syntax is important to see through. Keep trying, it will pay off.

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.