Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
bxprokop
New 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
MVP & Luminary
MVP & Luminary

Re: Year-To-Year Comparison

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
Contributor

Re: Year-To-Year Comparison

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)