Find the 3 mondays within 30 days

[dninus]

Hi All,

I am trying to display the store that have problem with conversion rate (Trasaction/traffic)( less than 20 and > 60%).
So far, this is what I got so far...
Store# WeekDay Date Trans Traffic Conv%
1 F 3/16/2012 446 87 19.5
1 S 3/18/2012 661 126 19.1
24 w 3/14/2012 153 94 61.4

The challenge is that I have to find the 3 same weekDay, in this case for store #1, I have to find 3 Fridays within that same period (30 days) and do the avg transactions and avg traffic.

Please help!

Many thanks!

 

[dninus]

I am using CR XI.

 

[lbass]

But what is the 30-day period? Let's say it is the month of March and we'll assume you limited the records in the selection formula to that month. You could insert a group on store, and then insert a second group on:

dayofweek({table.date})

Then you could simply insert summaries on the results.

If you mean in the last 30 days, then you would use a selection formula like this to limit the records:

{table.date} in currentdate-29 to currentdate

There would always be 4 or 5 instances of a particular dayofweek in a 30-day period though, so not sure how you want to determine which one(s) to exclude.

-LB

 

[Madawc]

DatePart will give you a day-number, so you can identify the day of the week for any date.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[dninus]

Thank you all for your reply.
I tried to take the date and subtract 7 from it to get the first Last Friday, subtract 14 to get the second last Friday and subtract 21 to get the 3rd last Friday.
The problem I am having is that, records are in the Date group footer, I can't get my formula to work because at that record level, the date is just that same date 03/16/2012. How can I get to the previous dates?

Thanks,

 

[dninus]

Hi All,

I got the 1stLastDay to work, but how can I get the traffic of the 1stLastDay? My gr
ouping is Store ->Period ->Weekofperiod ->Date. right now, all of my field is placing in Date group footer (because I have display the store and all the date that have problem under it)

Thanks,

 

[lbass]

Did you try my suggestion? Remove your date group and your week of period group and replace them with a day of week group, so that your final grouping is on Store->Period->DayofWeek.

-LB