Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
padmanabhan_ram
Creator II
Creator II

Avg Market Share Change

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

15 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
effinty2112
Master
Master

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%
CENTRAL754275580.21%
EAST915792801.34%
SOUTH97529585-1.71%
WEST69786967-0.17%
padmanabhan_ram
Creator II
Creator II
Author

Hi Gysbert,

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

Thanks,

Padmanabhan

padmanabhan_ram
Creator II
Creator II
Author

Hi Andrew,

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

Br,

Padmanabhan

settu_periasamy
Master III
Master III

Hi,

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

Capture.JPG

padmanabhan_ram
Creator II
Creator II
Author

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.

padmanabhan_ram
Creator II
Creator II
Author

Forgot attachment.

settu_periasamy
Master III
Master III

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.

Capture.JPG

settu_periasamy
Master III
Master III

Hi,

Have you got the solution?