Problem with excluding sites in a YTD Comparison, Example Attached

Thu, 16 Sep 2010 09:17:25 GMT

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.

Problem with excluding sites in a YTD Comparison, Example Attached

Thu, 16 Sep 2010 13:14:50 GMT

Does anyone know what i'm talking about? 🙂

Problem with excluding sites in a YTD Comparison, Example Attached

johnw — Fri, 17 Sep 2010 00:06:21 GMT

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.

topic Problem with excluding sites in a YTD Comparison, Example Attached in QlikView

Problem with excluding sites in a YTD Comparison, Example Attached

Problem with excluding sites in a YTD Comparison, Example Attached

Problem with excluding sites in a YTD Comparison, Example Attached