topic Re: Expression - not in in QlikView

Expression - not in

Mon, 14 Jul 2014 11:32:31 GMT

I have got a problem with expression where I would like to sum some data but only on working days:

sum(if ((NetWorkDays(MonthStart(Today()),Today()-1) = dzienRoboczyRejestrWniosku and (Num(Month(today()))=miesiacRejestrWniosku) and (dataRejestracjiWniosku=('*') - (Date('2014-08-15','YYYY-MM-DD'), Date('2014-11-01','YYYY-MM-DD'), Date('2014-11-11','YYYY-MM-DD'), Date('2014-12-25','YYYY-MM-DD'),

Date('2014-12-26','YYYY-MM-DD'), Date('2015-01-01','YYYY-MM-DD'),Date('2015-01-06','YYYY-MM-DD'),

Date('2015-04-05','YYYY-MM-DD'), Date('2015-05-06','YYYY-MM-DD'),Date('2015-05-01','YYYY-MM-DD'),

Date('2015-05-03','YYYY-MM-DD'), Date('2015-05-24','YYYY-MM-DD'),Date('2015-06-04','YYYY-MM-DD'),

Date('2015-08-15','YYYY-MM-DD'), Date('2015-11-01','YYYY-MM-DD'),Date('2015-11-11','YYYY-MM-DD'),

Date('2015-12-25','YYYY-MM-DD'),Date('2015-12-26','YYYY-MM-DD'))), kwotaWnioskowana, 0)))

But it doesn't work. Could anybody sugest me where is a problem ?

Re: Expression - not in

alexandros17 — Mon, 14 Jul 2014 11:40:04 GMT

Try the same changing the starting sum with rangesum

let me know

Re: Expression - not in

datanibbler — Mon, 14 Jul 2014 11:40:34 GMT

Hi,

do you have a master_calendar? If so, you could just calculate a binary field for working_days in there and make a set_expression out of it.

Regarding your scenarios - you are subtracting all the public holidays, it seems:

Networkdays returns just a number, so it's no good for distinguishing working_days and holidays.

I would suggest building a >> NOT(MATCH(Date, [list_of_holidays])) << condition in there - since your commands are in a language I don't know, however, it's a bit hard to say.

Re: Expression - not in

Mon, 14 Jul 2014 13:11:14 GMT

Thanks Data Nibbler. It works.