Categorization by amount of days overdue - account receivables

[Anonymous]

Hi guys and girls,

I need some help with creating custom columns in the query editor. I want to categorize overdue receivables by amount of days overdue. I have 6 categories:

1: 1-30 days

2: 31-60 days

3: 61-90 days

4: 91-180 days

5: 181-360 days

6: 360+

The table below provides the example of how my data looks and what data I want to create. the two first columns I have the last one I'm trying to calculate:

Number of days overdue	Amount Overdue	Category
23	12415	1-30 days
56	1352423	31-60 days
124	5374576	91-180 days
45	235234	31-60 days
123	12412415	91-180 days
765	2462342	+ 360 days
354	342	180-360 days
423	43636	+ 360 days
23	23413	1-30 days
12	235	1-30 days
34	24635745	31-60 days
65	23435	61-90 days
76	235426	61-90 days

Could someone please help me with the correct/easiest way to do this?

Thank you!

Br,

Me

[ashrin · Accepted solution]

Hi,

Create a new column like this :

category =

if(Table1[Column1]>=1 && Table1[Column1]<=30, "1-30 days" ,
if( Table1[Column1]>=31 && Table1[Column1]<=60, "31-60 days",
if( Table1[Column1]>=61 && Table1[Column1]<=90, "61-90 days",
if( Table1[Column1]>=91 && Table1[Column1]<=180, "91-180 days",
if( Table1[Column1]>=181 && Table1[Column1]<360, "181-360 days",
if( Table1[Column1]>=360, "360+ days"))))))

* Please mark this as answer if it helped 🙂

[Vvelarde]

@Anonymous

Hi, In Query Editor you can use Conditional Column (In Add Tab).

Regards

Victor