topic Networkdays calculation and date flags in QlikView

Networkdays calculation and date flags

stuwannop — Thu, 27 Jun 2013 09:47:31 GMT

Hi All

Hope you can help.

I have a list of dates in a table running from 1st Jan 2010 to date. I have created a calendar so some dates will be flagged if they fall within the financial year to date, starting 1st April 2013. So what I'm looking at is a table like this:

Date	FYTDFlag
31/03/2013	0
01/04/2013	1
02/04/2013	1
etc.etc...
30/06/2013	1
01/07/2013	0

Now what I need to do is is show the earliest and the latest dates in that range - at this point in time that would be 1st April 2013 and 30th June 2013. I would then calculate the networkdays once I have those two dates. I'm ok with the network days part, but how do I get the "min" and "max" dates based on the FYTD flag equalling 1.

I hope that explains what I'm after - thanks in advance for any help.

Stu

Re: Networkdays calculation and date flags

tresB — Thu, 27 Jun 2013 10:17:09 GMT

May be this:

forMax = FirstSoretdValue({<FYTDFlag={'1'}> Date, -Date}

forMIn = FirstSoretdValue({<FYTDFlag={'1'}> Date, Date}

Thanks.

Re: Networkdays calculation and date flags

stuwannop — Thu, 27 Jun 2013 11:30:51 GMT

Hi tresesco

Thanks for your response. It's pretty close i think but it's not quite working. If I use:

Firstsortedvalue(Date,Date) it will return the earliest(first) date in my data set - great so far. It's only when I enter the set analysis part that it returns a blank.

So, Firstsortedvalue({<FYTDFlag = {'1'}>} Date,Date) does not return a value. Syntax checker says it's ok.

Thoughts?

Stu

Re: Networkdays calculation and date flags

Thu, 27 Jun 2013 11:33:44 GMT

max({<FYTDFlag={'1'}>} Date)

min({<FYTDFlag={'1'}>} Date)

Re: Networkdays calculation and date flags

stuwannop — Thu, 27 Jun 2013 11:44:58 GMT

awesome - thanks Alex - I don't know what I was doing wrong but I'd got in my head that Max and Min don't work with set analysis.

Cheers

Stu