Return Sales and Share for Product's Top Selling Country

[lmcbasher]

Hi,

I have a table listing products and have created a measure which returns the country with the most sales in a given year:

=FirstSortedValue(Country,-aggr(sum({<Year={"$(=vSelectedYear)"}>}[Sales Volume]),[Product],Country))

This then creates a table listing products in one column and their top selling country in the next.

However, what i am struggling to do is return the sales volume itself for that country in that given year. I thought i could replace Country for [Sales Volume] at the start, but that doesn't seem the case.

I would also like to then calculate the product's market share for that same country and year. Market share being sales of the product over sales of all products for that country and year.

I assume annual growth should be straight forward once i can get the sales volume coming through, with the addition of $(=vSelectedYear-1)

Any support anyone can provide is appreciated!

[mdmukramali]

Hi Benjamin,

Have you found any workaround to Calculate the Total Sales Irrespective of the Product for Each Country ?

Maybe stalwar1‌ can help you to achieve it.

Thanks,

Mukram.

[lmcbasher]

Hi Mukram,

Sadly, not yet. Any ideas from anyone else would be appreciated!

Will be sure to post the solution once i have it.

Thanks again!

[sunny_talwar]

May be you need this

Sum(Aggr(If(FirstSortedValue(TOTAL <PRODUCT> COUNTRY,-aggr(sum({<Year={"$(=vSelectedYear)"}>}[SALES VOLUME]),[PRODUCT],COUNTRY)) = COUNTRY,

Sum(TOTAL <COUNTRY>{<[Year]={$(=vSelectedYear)}>}[SALES VOLUME])), PRODUCT, COUNTRY))

[lmcbasher]

Hi Sunny,

That works! Many thanks.

I have in fact just this minute received some other solutions to this as well. They are as follows:

Current Year Top Country Sales

Sum( Aggr( If(Rank(Sum({<Year={$(vSelectedYear)}>} [SALES VOLUME])) <=1, Sum({<Year={$(vSelectedYear)}>}[SALES VOLUME])),  PRODUCT, COUNTRY))

Previous Year Top Country Sales

Sum( Aggr( If(Rank(Sum({<Year={$(vSelectedYear)}>} [SALES VOLUME])) <=1, Sum({<Year={$(vPreviousYear)}>}[SALES VOLUME])),  PRODUCT, COUNTRY))

Top Country Total Sales (to get Share)

Sum( Aggr( If(Rank(Sum({<Year={$(vSelectedYear)}>} [SALES VOLUME])) <=1, Sum(total<COUNTRY>{1<Year={$(vSelectedYear)}>}[SALES VOLUME])),  PRODUCT, COUNTRY))

Big thanks for Mukram and Sunny for the help.

[sunny_talwar]

Great, if you got what you wanted, please close this thread by marking mdmukramali's response as correct and also selecting any helpful responses

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny