Function inWeekToDate

[#1]

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

[swuehl · Accepted solution]

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.

[#3]

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

[swuehl]

Maybe like

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

or

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

[#5]

awesome, the second option worked

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