Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)