topic Re: Year-To-Year Comparison in QlikView

Year-To-Year Comparison

bxprokop — Thu, 27 Sep 2018 22:58:31 GMT

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

Re: Year-To-Year Comparison

Gysbert_Wassenaar — Fri, 28 Sep 2018 04:13:13 GMT

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

Re: Year-To-Year Comparison

byrnel0586 — Wed, 17 Oct 2018 20:07:33 GMT

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)