Rank Grouping By Dimension expressions

[vidyasagar159]

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:

Rank	Dimensions
1	Top 3 Product Sales
2	Top 3 Product Sales
3	Top 3 Product Sales
4	Top 3 Product Actuals
5	Top 3 Product Actuals
6	Top 3 Product Actuals

Expecting:

Rank	Dimensions
1	Top 3 Product Sales
2	Top 3 Product Sales
3	Top 3 Product Sales
1	Top 3 Product Actuals
2	Top 3 Product Actuals
3	Top 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:  

Product	Sales Amount	Actual Amount	Risk Value	Return Value
A	60	5	20	15
B	50	100	25	25
C	70	70	55	35
D	20	35	15	30
E	10	25	17	15
F	5	30	20	20

Expected Result:

Dimension	Risk Value	Return Value
Average of TOP 3 Products by Sales Amount	33.33	25
Average of TOP 3 Products by Actual Amount	31.66	30

Thanks in Advance,

-Vidya

Message was edited by: vidya sagar malla

[swuehl]

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]

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]

and Yes i need a rank expression here.

Thanks,

-Vidya

[jomar_ebonite]

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]

Hi Jomar,

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

Thanks,

-Vidya