Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I want to find the company with the highest sales of the product 'Pencil'. I have a calculated dimension that works when all companies are selected. However, for some reason I need the dimension to work when only one company is selected and that does not work.
This is the dimension I use:
=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 2, Companies), Companies)
Does someone know the solution?
Many thanks!
Mark
Hi MArk, if you don't want the selections to filter you can use a dimension like:
=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 1, Only({1}Companies)), Companies)
(With "supress null values" checked)
and a expression like:
SUM({1< Year = {2014}, Product = {Pencil}>} Sales)
Hi MArk, if you don't want the selections to filter you can use a dimension like:
=AGGR(IF(RANK(SUM({1< Year = {2014}, Product = {Pencil}, Companies= >} Sales)) <= 1, Only({1}Companies)), Companies)
(With "supress null values" checked)
and a expression like:
SUM({1< Year = {2014}, Product = {Pencil}>} Sales)
Thank you Ruben, that worked perfectly for me!