Set Analysis - Compare actual promotion sales to sales last year for same period.

[Anonymous]

This is getting very complex and I need to take a step back and ask for some help!

I have Sales transactions with date and product. I have promotions with date and product that run for x weeks of time. I am trying to create a straight table that compares the actual sales for each product in each promotion to the actual sales for only those promo items in the same period last year. Actual sales for the promotion is easy: SUM(Sales). But I am having a hard time getting the prior year period to display correctly with set analysis.

I need something like this:

PromoName	Product	Actual	Prior Year
promo1	abc	500	485
promo1	def	300	320
promo1	xyz	400	375
promo2	abc	350	380

I have tried various set analysis and am getting my date range, but cannot seem to include only product from the promo. In addition, including the dimension of promoname causes the set expression to add rows and try to fit the sales into a null or another promo that happened to be running during that period the prior year. Can someone help me with the logic here? I can explain what I need in English, but I can't seem to explain it at the set level.

Thanks!

[johnw]

I gather that someone has already selected one and only one year if sum(Sales) is returning the right value. Then I'm guessing that you're doing something like this for the prior year column?

sum({<Year={$(=only(Year)-1)}>} Sales)

And the problem with that is that it's giving you ALL sales for last year, not just sales for this year's products?

I'm guessing it might be something like this:

sum({<Year={$(=only(Year)-1)},Product=p(Product)>} Sales)

The idea there is to use p(Product) to say restrict the products to only those that are possible given the current selections, so products for the current year and the promos you're showing. I'm not 100% sure I have that right, though. I'm also not very confident that I've understood your question.

So if that's the wrong answer, or if it's obvious I've misunderstood, and if nobody else pops out with the right answer, can you give us some raw data, and what you want your chart to look like for that raw data? Ideally in a QVW with an inline load so that I don't have to build one manually from your raw data.

[Anonymous]

Thanks John for your reply.

I have attached a simple example so maybe we can see what I am trying to accomplish a little more simply. I appreciate anyone's input

[johnw]

Thanks for the example. This expression sort of works:

SUM({<Product=p(Product),PromoName=,Date={">=$(=ADDMONTHS(MIN(Date),-12)) <=$(=ADDMONTHS(DATE(MAX(Date)+6),-12))"}>} Sales)

The first addition is the Product=p(Product) that I mentioned before. The second is PromoName=. That's there because the product last year didn't have a PromoName, so we have to explicitly tell the set analysis to ignore our selection.

But we still have a problem as a result of the product last year not having the PromoName. Since you used PromoName as a dimension, that makes the 1100 and 900 appear on separate lines instead of on the same line.

It's probably a step in the right direction, though. I'll keep thinking about it.

[Anonymous]

Great step there. That gets me almost there. But as you brought up, the big problem now is that the result does not appear in the correct row as I must use the PromoName as a dimension. Further, if you select multiple PromoNames or unselect all PromoNames, this messes it up further since multiple Promo's worth of data gets lumped into a row with no PromoName. In the real data, this gets even more confusing because various promos run all year and instead of lumping everything into a "null" field, there might have been a similar promotion running last year. So it attributes the sum to a different Promotion.

Now normally an option would be to prevent the object from calculating if getselectedcount(PromoName)<>1, but the business requires multiple Promotions to be shown at once for comparison purposes which is why I must use the PromoName or similar field as a dim.

But I am encouraged that we are getting closer. Thanks for your assistance.

[johnw]

I'm not sure this solves ALL of your mentioned problems, but I think it might be a better direction to take than set analysis.

I separate the raw data into a Sales table and a Promotions table. The Promotions table links to sales by Product, Date, AND a PeriodType of 'Promo Year' or 'Prev Year'. The 'Prev Year' PeriodType links to the promotion directly to the comparable sales in the previous year, even though they weren't part of the promotion itself. That lets you create a pivot table with PeriodType as a new dimension across the top, and then sum(Sales) will give you the correct numbers with no complicated expressions. Seems to work, at least on the surface.

I'm betting there's a simpler way to set up the Promotions table than what I did, though.

[Anonymous]

So that did the trick. The client wants this in a pivot with several other expressions so I need these as expressions. I changed it to a straight table and the following seemed to work:

SUM({$<PeriodType={'Promo Year'}>}Sales)
SUM({$<PeriodType={'Prev Year'}>}Sales)

The logic appears sound. I am able to click on/off the promos and the results flow in one row and seem to reflect correct numbers. Of course the client also wants a table with a rolling prior period equal in length to the original promo just previous to the promo, but I will follow the same logic to get there.

Thanks again for your help John! You are a great asset to this community.

Regards,

Aaron Couron