Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try avg(aggr(Above(sum(Volume))/sum(Volume)-1,REGION,WeekEndingDate))
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% |
Hi Gysbert,
Thanks for your response, but the above expression doesn't give the expected output.
Thanks,
Padmanabhan
Hi Andrew,
Thank you for your response, but the requirement is to get the above.
Br,
Padmanabhan
Hi,
Can you provide the formula in excel (based on your excel file) like the below snapshot..
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.
Forgot attachment.
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.
Hi,
Have you got the solution?