Link to home
Start Free TrialLog in
Avatar of lkudsk

asked on

How do I remove week no from table

Hi everybody

I have made a cross-tab with the numbers incident per week (EU standard - week from Monday to Sunday). I got my week numbers by using this formula:

IIF( DatePart("ww",{},crMonday,crFirstFourDays) > 53, 53,   DatePart("ww",{},crMonday,crFirstFourDays))

But when I run the report in week 7 I only want the report to show week 1, 2, 3, 4, 5 and 6 (and not week 7 - bacause it's not a full week yet)
I only want the report to show full weeks - the report should only show week through a full week (in this example only to week 6).

When I run the report next time and current week is 9 it should only show the week from 1-8.
See attached screen dump of my reports as It is for now - showing current week.

 User generated image
Avatar of James0628

When you say that you want to see full weeks, are you saying that if you run the report on Sunday, you want to see everything up to that day (because the week ends on Sunday), but if you run it on any other day, you only want to everything up to the previous Sunday?

 If so, do you want to exclude the partial week from the report completely, or do you need it in the report for some other purpose and only want to exclude it from the cross-tab?

 Assuming that you want to include everything after the prior Sunday from the report, or include everything if today is Sunday, you could add something like the following to your record selection formula.  I did some tests here and it seems to return today if it's Sunday, or the previous Sunday if today is not Sunday.

{} <= DateAdd ("d", -(DayOfWeek (CurrentDateTime, crSunday) - 1), CurrentDateTime)

 If includes the time, then you may need to adjust the formula for that.  For example:

{} <=
 DateTime (Date (DateAdd ("d", -(DayOfWeek (CurrentDateTime, crSunday) - 1), CurrentDateTime)),
 Time ("11:59:59 PM"))

Avatar of lkudsk


Now I have found out how to remove week 7 (for this exsampel) in this report but only in Details not in the "cross-tab".

I use Section Expert. In section expoert I have chosen "Suppress (No-Drill Down)" and used this formula: {@Uge nr.} = {@Uge test}

Where {@Uge test} is: DatePart("ww",CurrentDate,crMonday,crFirstFourDays)
That is working for my Detail sections but I don't know how I'm doing the same exercise with the "Cross-Tab"
Avatar of lkudsk


Sorry I only want to see previous fullweek.

If I am in week 7 (as for now) I only want to see uptil week 6
If I am in week 14 I only want to see uptil week 13
Avatar of lkudsk


I just want to exclude it from the cross-tab
Avatar of James0628

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You said that you had used that formula to suppress the details, so I assumed that you didn't want to include those records in the report at all.  But then you said "I just want to exclude it from the cross-tab".

 So, do you want to see those records in the details or not?  Maybe suppressing the details was just an exercise, to see if you could, but you really want to see those records in the details?