Age Analysis Using QUERY Function in Google Sheets [Step by Step Guide]

0
62

It’s quite simple to create an Age Analysis Using Query function in Google Sheets. As I told you many times, in order to enjoy the full benefit of Google Sheets, you should learn the QUERY function in it. Here is my humble effort to explain you, how to use Google Sheets Query to create an age analysis report.

Featured Similar Alternative Options and Guides:

So let’s begin our step by step guide to create Accounts Departments’ Age Analysis Using Google Sheets QUERY Function.

Auto Generate Age Analysis Using QUERY Function in Google Sheets

Sample Data:

From the above sample data, we can create an age wise analysis report. If you are not following the above format in your office, please try to modify that to match with our sample data or create our own above sample data in a new sheet to learn this trick.

In an age analysis report, the invoice values would be under categories such as Current, 30 days, 60 days, 90 days or longer based on their ageing. So that we can easily check the due payments as per our agreed payment terms with the customer or client.

See a Sample Ageing Analysis Report using Query function first. Please note that, I didn’t apply much formatting to the report. I only concentrated on the data and formula part.

Steps Involved in Creating Age Analysis Using QUERY Function in Google Sheets:

To use Query function to generate age analysis report, we should first add two more columns to our sample data above. What are those two columns?

See Column E and F in the below screenshot. There in Cell E2 and F2 we need to apply different formulas as below and then copy and paste it to downwards.

Here is that formulas:

Note: I suggest you to type the formulas on your sheet instead of copy and paste from here.

Cell E2: I’ve just keyed in a Google Sheets DATEDIFF function.

=DATEDIF(C2,TODAY(),”D”)

Cell F2: Here I’ve used Google Sheets IF Logical Function.

=if(E2<30,”[1] Current”,
if(and(E2>=30,E2<60),”[2] 30 days”,
if(and(E2>=60,E2<90),”[3] 60 days”,”[4] 90 days Above”)))

Now we are set to use our QUERY formula on next sheet or same sheet to create an age analysis report. Here is that formula.

={query(‘Pivot Table’!A1:F10,”Select B,C, sum(D) group by B,C pivot F”,1);“Total > >”,””,transpose(query(‘Pivot Table’!A1:F10,”Select sum(D) group by F label Sum(D)””,1))}

You just need to use this formula on any other sheet on the file. It will instantly generate the age wise report.

Explanation to the Age Analysis Query Formula

I’ve used the Pivot Query Clause with the function to achieve generating age wise analysis report.

There are two parts in the formula which you can see joined by Curly Brackets. While the first Query formula generates the Ageing Analysis Report, the second formula is intend to generate a total row at the end of the Query report.

If you want to go in-depth in the use of Query function, I recommend you to check the below two Google Sheets guides from me.

I am sharing my example sheet of Age Analysis where I’ve applied the above formulas. You can make a copy of that file from the file menu and use it as a template for your use. Any doubt, please fell free to use the comment box below. Enjoy.

Age Analysis Using Query – Access Sheet

SHARE

A technology enthusiast and addictive blogger who likes to travel and wish to mingle with different community of people from around the world.