Problem with excluding sites in a YTD Comparison, Example Attached

[#1]

Hi All

Please see in the Attached Demo File, what i am trying to do. I only want a comparison of the possible Stores, not the new Stores that are only active in 201007, i hope this is enough information. If not please let me know.

Thanks.

[#2]

Does anyone know what i'm talking about? 🙂

[johnw]

Add this:

StoreName*=P({1<YearMonth = {$(vPYMaxDate)}>} StoreName)

Which gives you this:

=num(sum({1<YearMonth= {$(vMaxDate)}, AdvertiserNew = {'Aspen-Pharmacare'},TerminalsFlag = {'1'},ManufacturerGroup = {'Aspen-Pharmacare'},StoreName*=P({1<YearMonth = {$(vPYMaxDate)}>} StoreName) >} Sales ),'R#' )

The extra condition says to intersect the StoreNames that would have been "selected" with the StoreNames that were posssible (had sales) the same month last year. So you'll only include StoreNames that have sales in this month both this year and last year. I believe that's what you're asking for. However, I'm worried that it's a mistake. What do you want to do if a store had sales last year this month, but NOT this year this month? Currently, you'd see sales going down, even if other stores have replaced the one you lost. Not sure if that's really what you're after, but maybe it is.