topic Re: Function inWeekToDate in QlikView

Function inWeekToDate

Wed, 25 Nov 2020 16:16:04 GMT

Hi I am new to QlikView I'm trying to provide the number of proposal request that are due within the current week. In researching the community I found the function inWeekToDate, I believe I can use that with Today() to determine if it's in the same week and return a 0?

For example in my data

Today is March 21st, I have 6 distinct request numbers that have estimations due this week.

I use the following formula and get 0

=count(distinct if(inWeekToDate([Estimation End Date],Today(),0),[Proposal Request Number]))

fyi - Once I get this one working, I'll want to see the ones that are late (past due) or the ones that are due next week.

Any help on this will be great.

Thanks

Liz

Re: Function inWeekToDate

swuehl — Mon, 21 Mar 2016 17:54:28 GMT

Instead of InWeekToDate(), use InWeek()

InWeek (date, basedate , shift [, weekstart])

returns true if date lies inside the week containing basedate. The week can be offset by shift. Shift is an integer, where the value 0 indicates the week which contains basedate. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekstart. This may be given as a real number indicating days and/or fractions of a day.

Re: Function inWeekToDate

Mon, 21 Mar 2016 18:14:47 GMT

Thanks so much! That worked. So if I want any that are pass due what do I do?

=count(distinct if(inWeek([Estimation End Date],Today(),-1),[Proposal Request Number]))

will only give me last weeks correct? I want any that are late

Re: Function inWeekToDate

swuehl — Mon, 21 Mar 2016 20:02:46 GMT

Maybe like

=count(distinct if( [Estimation End Date]<Today() ,[Proposal Request Number]))

=count(distinct if( [Estimation End Date]<weekstart( Today()) ,[Proposal Request Number]))

Re: Function inWeekToDate

Mon, 21 Mar 2016 20:18:59 GMT

awesome, the second option worked

=count(distinct if([Estimation End Date]<weekstart( Today())and [Estimate Complete]='No',[Proposal Request Number]))