topic Re: Avg Market Share Change in QlikView

Avg Market Share Change

padmanabhan_ram — Tue, 15 Dec 2015 15:02:11 GMT

Hi All,

I am trying to find out the average market share change per week in the attached qvw.

My expected output is

National 0.13%

East 0.24%

South -0.01%

Central 0.15%

West 0.12%

the set analysis expression which I have created in the attached qvw seems to be missing something.

can anyone please help me to get the proper set analysis to get the value.

Br,

Padmanabhan

Re: Avg Market Share Change

Gysbert_Wassenaar — Tue, 15 Dec 2015 15:30:02 GMT

Try avg(aggr(Above(sum(Volume))/sum(Volume)-1,REGION,WeekEndingDate))

Re: Avg Market Share Change

effinty2112 — Tue, 15 Dec 2015 15:43:07 GMT

Hi Padmanabhan,

For the calculation you're wanting to do I don't think it's meaningful to calculate an average of the weekly changes. I think it makes sense to consider the difference between the first and last figure for each region and divide by the first figure. Adding up weekly percentage differences and dividing by three I don't think makes sense.

I'm hampered a little because I only have a PE license and can only access your data by a binary load so I can't see your UI objects.

I may be wrong and you may disagree but these are the figures I get

First Week = sum({$<WeekEndingDate = {'$(=min(WeekEndingDate))'}>}Volume)

Last Week = sum({$<WeekEndingDate = {'$(=max(WeekEndingDate))'}>}Volume)

REGION	First Week	Last Week	% Change
	33429	33389	-0.12%
CENTRAL	7542	7558	0.21%
EAST	9157	9280	1.34%
SOUTH	9752	9585	-1.71%
WEST	6978	6967	-0.17%

Re: Avg Market Share Change

padmanabhan_ram — Wed, 16 Dec 2015 04:26:04 GMT

Hi Gysbert,

Thanks for your response, but the above expression doesn't give the expected output.

Thanks,

Padmanabhan

Re: Avg Market Share Change

padmanabhan_ram — Wed, 16 Dec 2015 04:33:39 GMT

Hi Andrew,

Thank you for your response, but the requirement is to get the above.

Br,

Padmanabhan

Re: Avg Market Share Change

settu_periasamy — Wed, 16 Dec 2015 05:05:47 GMT

Hi,

Can you provide the formula in excel (based on your excel file) like the below snapshot..

Re: Avg Market Share Change

padmanabhan_ram — Wed, 16 Dec 2015 06:27:21 GMT

Hi Periasamy,

please find the excel attached, on the right hand side my output is available only for Central region.

remember to include product while performing the calculations, since it was not there in your attached excel so i didn't modify it. But in my original data it is available. See previous attachments for your referrence.

Re: Avg Market Share Change

padmanabhan_ram — Wed, 16 Dec 2015 06:28:30 GMT

Forgot attachment.

Re: Avg Market Share Change

settu_periasamy — Wed, 16 Dec 2015 07:18:33 GMT

Hi Padmanabhan,

Check the attachment. I just added the dimension. If you don't want that, you can remove it.

The formula i tried

sum(aggr(((sum(Volume)/sum(TOTAL <REGION>Volume))-Above(sum(Volume)/sum(TOTAL <REGION>Volume))),REGION,ProductFranchise,WeekEndingDate)))

Maybe someone will give the simplify expression.

Re: Avg Market Share Change

settu_periasamy — Thu, 17 Dec 2015 07:24:21 GMT

Hi,

Have you got the solution?

Re: Avg Market Share Change

padmanabhan_ram — Thu, 17 Dec 2015 07:41:12 GMT

Hi,

No, when i try to do it for individual products it gives wrong value.

Re: Avg Market Share Change

settu_periasamy — Thu, 17 Dec 2015 07:46:49 GMT

I think, it is giving the correct value..

Re: Avg Market Share Change

pandiarajan — Thu, 17 Dec 2015 10:04:38 GMT

Hi Periasamy,

as per ur logic aggregation of total calculating all region volume value . but as per Padmanabhan.Ram requirement total shoul be calculation only particular region . please Padmanabhan.Ram correct me suppose if i am wrong in the logic

with regards,

Pandiarajan.R

Re: Avg Market Share Change

settu_periasamy — Thu, 17 Dec 2015 10:17:32 GMT

Hi Pandiarajan,

I just included the Dimension for detailed value. we can remove, if we don't need it. It will give the value based on Aggregation(Region,WeekEndingDate,Product)

Re: Avg Market Share Change

pandiarajan — Thu, 17 Dec 2015 10:39:37 GMT

Hi periasamy,

you should take aggreagation only Region Dimension . suppose if apply this logic value coming correct but only display the first row of the region. please find below image then i have attached qvw file as well as

Re: Avg Market Share Change

settu_periasamy — Thu, 17 Dec 2015 13:00:33 GMT

Yes Pandiarajan. The Working Sheet Tab is incomplete sheet which is created by me, just for my reference. As you said, we need to create the aggregation for the 3 dimensions.Main Sheet is Original Calculation.

Check the Attachment now..