Year-To-Year Comparison

[bxprokop]

Hi,

I am trying to do a year-to-year comparison comparing a Product %-To-Total but exclude Product that weren't used in one year or the other.  Basically, I am looking to compare apple to apples. The formulas I am using are as follows:

(sum({<YEAR={'2018'},PRODUCT={'A','B'}>} VOLUME)/
sum({<YEAR={'2018'}>} VOLUME))-
(sum({<YEAR={'2017'},PRODUCT={'A','B'}>} VOLUME)/
sum({<YEAR={'2017}>} VOLUME))

How do I adjust this formula to accomplish my objective?  In addition, I need the overall results to be the same when using other dimensions such as State, City, Region, etc.  Any help would be greatly appreciated

Thanks

Brian

[Gysbert_Wassenaar]

Perhaps with this set modifier for the PRODUCT field: PRODUCT=P({<YEAR={2017}>}PRODUCT)*P({<YEAR={2018}>}PRODUCT)

[byrnel0586]

Not sure if this will help, but here is an example of using the P & E functions to include or exclude variables

1. Shows the sales of all customers who have purchased something from the Product field value, "Business Buffet".

sum({$<Customer = P({1<Product={'Business Buffet'}>} Customer)>}Sales)

2. Shows the sales of all customers who have not purchased something from the Product field value, "Business Buffet".

sum({$<Customer = E({1<Product={'Business Buffet'}>} Customer)>}Sales)