Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There,
I'm a bit stuck on a small problem I have and any suggestions would be much appreciated......
I have a Date field 'Invoice_Date' being used as a dimension in a bar chart.
I would like to group these invoices into 6 catagories Based on their due date, ie. whether the invoice is due at some point in the future or at some point in the past. These catagories would be relative to today 'today()'
The categories would be as follows:
Invoice due in 15 days +
Invoice due in 8-14 days
Invoice due in 0-7 days
Invoice overdue 1-7 days
Invoice overdue 8-14 days
Invoice overdue 15 days +
I gather I need a calculated dimension made of a chain of 'if' statements but cant quite get it to work.
Any suggestions much appreciated : )
if d is the date field add a calculated dimension
=
if(d<(today()-15), dual('<15',-15),
if(d<(today()-8), dual('<8',-8),
if(d<today(), dual('<0',0),
if(d<(today()+8), dual('<-8',8),
if(d<(today()+15), dual('<-15',15),
dual('other',100)
)))))
Use Class function with a width of 10 days is this is ok
make this as calculated dimension
class( Today(0) - invoiceDate ,7 )
and your expression
should be the count or sum of invoices
Count(Invoices) or sum(invoices)
The Class Function will return a data like this
=Class(Today(0)-InvoiceDate,10) | count(Value) |
93 | |
160 <= x < 170 | 3 |
170 <= x < 180 | 10 |
180 <= x < 190 | 10 |
190 <= x < 200 | 8 |
530 <= x < 540 | 8 |
540 <= x < 550 | 10 |
550 <= x < 560 | 10 |
560 <= x < 570 | 3 |
890 <= x < 900 | 3 |
900 <= x < 910 | 10 |
910 <= x < 920 | 10 |
920 <= x < 930 | 8 |
in order to make it look better use the replace function to eleiminate <= and < to -
Use replace() function for replacing '<= x <' to -
Replace(Class(Today(0)-Date,10),'<= x <','-')