topic Set Analysis in QlikView

Set Analysis

Tue, 18 Oct 2016 05:27:38 GMT

Hi all,

I have a line chart with MonthName and Product_L as my dimension and I want to calculate the share of each product over time. so my numerator is sum of the attribute (say sales) for the product and demonimator is sum of the same attribute (Sales) for all products. For this I am using the expression

Num((sum({$<CATEGORY={'begin','win'},Specialty={'$(vSpecialty_L)'},Indication={'$(vIndication_L)'},MonthName=, MonthID ={'<$(=$(vLast12Month))'}>}Sales)/num(sum({<PRODUCT_L=,CATEGORY={'begin','win'},Specialty={'$(vSpecialty_L)'},Indication={'$(vIndication_L)'},MonthName=, MonthID ={'<$(=$(vLast12Month))'}>}Sales),'$(vFormat)')),'$(vFormat)')

But the share is always shown as 100 % , for some reason it si not respecting PRODUCT_L= in the denomator . Any reason for it ?

Thanks

Shivraj

Re: Set Analysis

sunny_talwar — Tue, 18 Oct 2016 15:55:56 GMT

Try this:

Num(

(Sum({$<CATEGORY={'begin','win'}, Specialty={'$(vSpecialty_L)'}, Indication={'$(vIndication_L)'}, MonthName=, MonthID ={'<$(=$(vLast12Month))'}>} Sales)

Sum(TOTAL <MonthName> {<PRODUCT_L=,CATEGORY={'begin','win'},Specialty={'$(vSpecialty_L)'},Indication={'$(vIndication_L)'},MonthName=, MonthID ={'<$(=$(vLast12Month))'}>}Sales))

,'$(vFormat)')

Re: Set Analysis

sunny_talwar — Tue, 18 Oct 2016 15:56:46 GMT

Or may be this:

Num(

(Sum({$<CATEGORY={'begin','win'}, Specialty={'$(vSpecialty_L)'}, Indication={'$(vIndication_L)'}, MonthName=, MonthID ={'<$(=$(vLast12Month))'}>} Sales)

Sum(TOTAL <PRODUCT_L> {<PRODUCT_L=,CATEGORY={'begin','win'},Specialty={'$(vSpecialty_L)'},Indication={'$(vIndication_L)'},MonthName=, MonthID ={'<$(=$(vLast12Month))'}>}Sales))

,'$(vFormat)')

Re: Set Analysis

Wed, 19 Oct 2016 06:30:11 GMT

Hi Sunny,

Many thanks , this works . Just wants to understand the purpose of putting

total, is it taking all the months ?

Thanks

Shivraj

Re: Set Analysis

sunny_talwar — Wed, 19 Oct 2016 16:28:21 GMT

I think it would be best if you can read about TOTAL Qualifier here: What does the TOTAL qualifier do?

Also, if you got what you wanted, I would suggest you to close this thread down by marking the correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny