Alternate States Like for Like

Thu, 11 Jun 2015 13:56:18 GMT

Howdy.

I am using alternate states to compare sales form one period to another. all of this is working quite nicely.

But a recent request has be completely stumped.

I need to show sales per Retailer group, but only on a like for like basis.

Example

Retailer is Woolworths. I have 4 agents, call them A,B,C and D.

In 2014 A,B and C were active.

In 2015 A,B and D is active, Agent C has closed down.

What I now need is to sum up the sales for A and B only, ignoring C and D because they are not in both states.

I have the following expression

=sum({Previous< BPRetailerGroup=$:: BPRetailerGroup >} NettSalesQty)

=sum({Current< BPRetailerGroup=$:: BPRetailerGroup >} NettSalesQty)

They sum up the values for all the agents in their timeframe.

I need them to link up and see that only A and B are in both states, and only work with those two.

How do I do That?

Thanks in advance..

Ray

Re: Alternate States Like for Like

swuehl — Thu, 11 Jun 2015 16:49:19 GMT

You should be able to do this using the p() function for implicite field value definitions:

Maybe something along these lines:

=sum({Previous< BPRetailerGroup=$:: BPRetailerGroup, Agent = p() * p({Current}) >} NettSalesQty)

=sum({Current< BPRetailerGroup=$:: BPRetailerGroup, Agent = p() * p({Previous}) >} NettSalesQty)