Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
bxprokop
Contributor II
Contributor II

Year-To-Year Comparison

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

2 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
byrnel0586
Creator
Creator

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)