topic Re: Nested if Statement to create Sub-Fields from my Date Field in QlikView

Nested if Statement to create Sub-Fields from my Date Field

Thu, 16 Apr 2015 19:42:54 GMT

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 : )

Re: Nested if Statement to create Sub-Fields from my Date Field

maxgro — Thu, 16 Apr 2015 20:17:47 GMT

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)

)))))

Re: Nested if Statement to create Sub-Fields from my Date Field

ziadm — Thu, 16 Apr 2015 20:46:16 GMT

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 <','-')