Simple filter to display historical data.

[Former Member]

I have a report that displays sales based on the week number.

My report is set to go out on Monday afternoon and needs to show sales for all previous weeks. The report is built to show the week number and in column 2 the total sales.

I have a specific "last week" report that I also send out on Mondays and it uses the following filter to ensure I am only capturing the previous weeks sales:

=Week(RelativeDate(LastDayOfWeek(CurrentDate());-7))

In my other report I need to show all sales of previous weeks.

Currently it is showing the sales for today (Monday) of this week. The requirement is to show all the previous weeks.

[Former Member]

Amit,

Thanks for the reply, I failed to mention that I have all of the weeks listed, see screenshot below (please disregard years in header)and when I apply your filter, it filters all the other data as well. I am counting the quotes using:

=Count([QUOTES].[Quote Number])

How can I create a variable using a mix of both of these formulas so it only accomplishes what I need?

[amitrathi239]

if you want to show the previous week data only in the column then use where clause.

=Count([QUOTES].[Quote Number]) where ([your week variable]=Week(CurrentDate())-1)

[amitrathi239]

try this for previous week data.

Var=if([your week variable]=Week(CurrentDate())-1) then 1 else 0

Apply filter on the table where equal to 1 to get the previous week data only.