topic Re: Creating bandings 0-6, 6-12, etc in QlikView

Creating bandings 0-6, 6-12, etc

Mon, 19 Jan 2015 16:33:38 GMT

So i have found out how many working days between two dates.. but now i want to band the results by months.

So 0-6mts, 6-12mths, 12-18mts, 18-24mts, 24-36mts, >36mts

Below is my script but im getting errors say you are only allowed 3 if conditions.. I didnt know that..

Any help

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=6,'0-6mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >6 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=12,'6-12mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >12 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=18,'12-18mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >18 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=24,'18-24mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >24 and if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=36,'24-36',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >36,'>36mts','Error'))))))) as Banding

Thanks

Re: Creating bandings 0-6, 6-12, etc

Mon, 19 Jan 2015 16:45:10 GMT

Hi Andrew,

you are trying to do a second IF as part of your AND statement when you want both conditions,

>6 And <12

not >6 And If <12

so the number of parameters on your IF's are going wrong.

You are evaluating these in sequence though, you don't actually need to the second condition in each IF,

basically anything under 6 months is caught in your first true section, so the only possible false has to be greater than 6 and so you only then need to check less than 12 etc..

So I think the below should work for you

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=6,'0-6mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=12,'6-12mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=18,'12-18mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=24,'18-24mts',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 <=36,'24-36',

if(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30 >36,'>36mts','Error')))))) as Banding

hope that helps

Joe

Re: Creating bandings 0-6, 6-12, etc

Mon, 19 Jan 2015 16:56:36 GMT

Thanks very much.. that worked... don't know why i was doing it that way... i had my excel cap on there...

Re: Creating bandings 0-6, 6-12, etc

Mon, 19 Jan 2015 17:03:44 GMT

No problem,

I should probably add that, you could do this with a simple mapping table instead of all the nested if statements if you wanted.

Just create a new mapping table along the lines of

Month, Group

1, 0-6 mths

2, 0-6 mths

3, 0-6 mths

4, 0-6 mths

5, 0-6 mths

6, 0-6 mths

7, 6-12 mths

etc

then you can just use applymap on you base function(with floor)

Applymap( 'Your mapping field here', Floor(NetWorkDays(Notificationdate,Date#('01'&'/'& right(ReportMonth,2)&'/' & left(ReportMonth,4),'DD/MM/YYYY'))/30) , 'Error' ) As banding

just a thought really

hope that helps

Joe