Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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

2 Replies
MVP
MVP

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

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)

)))))

ziadm
Valued Contributor

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


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 < 1703
170 <= x < 18010
180 <= x < 19010
190 <= x < 2008
530 <= x < 5408
540 <= x < 55010
550 <= x < 56010
560 <= x < 5703
890 <= x < 9003
900 <= x < 91010
910 <= x < 92010
920 <= x < 9308

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