Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vidyasagar159
Contributor II

Rank Grouping By Dimension expressions

Hello Everyone,

I want to rank the products based on the two measures (Sales and Actuals). With this expression, I was getting the following results. But what I was expecting is different. Can you please help me in building the right expression.

=if(aggr(rank(sum([Sales])),[Product ID])<= 3,'Top 3 Product Sales',

if(aggr(rank(sum([Actuals])),[Product ID])<= 3,'Top 3 Product Actuals'

))

Result:

RankDimensions
1Top 3 Product Sales
2Top 3 Product Sales
3Top 3 Product Sales
4Top 3 Product Actuals
5Top 3 Product Actuals
6Top 3 Product Actuals


Expecting:

RankDimensions
1Top 3 Product Sales
2Top 3 Product Sales
3Top 3 Product Sales
1Top 3 Product Actuals
2Top 3 Product Actuals
3Top 3 Product Actuals

Question Modified from above:

From the below sample table. I want to create the dimension expression pivot table. I considered using the ranking for this approach but I am not getting the results as expected.

sample data table:  

     

ProductSales AmountActual AmountRisk ValueReturn Value
A6052015
B501002525
C70705535
D20351530
E10251715
F5302020

Expected Result:

   

DimensionRisk ValueReturn Value
Average of TOP 3 Products by Sales Amount33.3325
Average of TOP 3 Products by Actual Amount31.6630

Thanks in Advance,

-Vidya

Message was edited by: vidya sagar malla

5 Replies
MVP
MVP

Re: Rank Grouping By Dimension expressions

Your dimension seems to return the expected results. I guess you are actually asking about the Rank expression?

How have you defined this expression? I would assume quite similar to your dimension?

vidyasagar159
Contributor II

Re: Rank Grouping By Dimension expressions

Hi Stefan,

Yes, I defined this expression. But I was not getting the results as expected resultset. I am developing this in a Qlik Sense.

Thanks,

-Vidya

vidyasagar159
Contributor II

Re: Rank Grouping By Dimension expressions

and Yes i need a rank expression here.

Thanks,

-Vidya

jomar_ebonite
New Contributor III

Re: Rank Grouping By Dimension expressions

Hi,

Try this in expression not in dimension.

=if (rank (total (sum([Sales])))<= 3,'Top 3 Product Sales',

if (rank (total (sum([Actuals])))<= 3,'Top 3 Product Actuals'

))

vidyasagar159
Contributor II

Re: Rank Grouping By Dimension expressions

Hi Jomar,

The Expression is not working as expected. let me update the question with some sample data.

Thanks,

-Vidya

Community Browser