I generate a simple sales report which sums up quantities by product so that we can estimate how much inventory we need.
One product is a set of various items also sold individually. The product was recently updated to include more items. I want two separate summary totals based on date ranges representing the time before and after the change so that I can create a calculation field which totals the products sold individually and in the set. Sorry if I'm not expressing this very clearly.
Eg. Before Sept 2017 product GC1 is sold individually and 3 of this item are also included in the set GCVP, but product GC2 is only sold individually.
After Sept 2017, both product GC1 and GC2 are sold individually and also 3 of each are included in the set GCVP.
So far, I've just been multiplying the GCVP Sum by 3 and adding it to the GC1 Sum to get a total, but that won't work for GC2.
Is there an easy solution?
Thank you in advance for any suggestions!