I am trying to create a tab that shows delinquent members based on another tab.
My sample data is located here: https://docs.google.com/spreadsheets/d/1hyBfQBYF1TNmVgXammI5vADWFCD2d1eA/edit?usp=sharing&ouid=105919111240161574477&rtpof=true&sd=true
If you look at the 2023 Delinquent Members tab, you'll notice that there are 2 entries: Betty Rubble and Wilma Flintstone. But if you look at the 2023-TreasurerWorksheet, notice that Wilma actually paid under 2 different names, one being her married name and one being her maiden name. So, she's actually overpaid. She was only supposed to pay $100 and she's paid $125. How do I get the report to not show Wilma as delinquent and still keep the others (Betty in this case) as delinquent ?
I was going to do some calculations using the ABS() to get the absolute value and add that for column F of the TreasurerWorksheet, but it looks like you can't use ABS in a QUERY.
Any help would be appreciated. Thanks in advance!
ABS function isn't useful here as it just transforms every number to non-negative.
To solve the question you mentioned, we can first select the required columns and SUM 'total collected' over GROUP BY 'Active Name',i.e. Column B in this case. Hence the following query would work to get total amount to be paid by each person:
select B,SUM(D),MAX(E),MAX(E)-SUM(D) WHERE B is not null and C contains 'Current Member' GROUP BY B
But, in this case, we only want to include the people with due amount, hence we will need to add a case WHERE the difference between total amount to be paid and paid amount is greater than 0. Note that all the negative amounts are overpaid.
Hence, we can add the condition on the subquery mentioned above:
=QUERY(QUERY('`Range`,"select B,SUM(D),MAX(E),MAX(E)-SUM(D) WHERE B is not null and C contains 'Current Member' GROUP BY B"), "WHERE Col4>0")