Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vidyasagar159
Contributor II

Multiple Dimension aggregation Columns

Hello Everyone,

From the below sample data table. I want to create an aggregational table and later I will convert it to the scatter plot which shows aggregation of the Products based on their sales amount and Actual amounts. 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

Summary:

Average of TOP 3 Products by Sales Amounts: C,A,B are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.

Average of TOP 3 Products by Actual Amounts: B, C, D are the Top 3 Products. Now I want Avg(Risk Value) and Avg(Return Value) of these Products.


Expressions I am using:


Dimension Expression:


if(aggr(rank(sum( [Sales Amount])),[Product ID])<= 3,'Average of TOP 3 Products by Sales Amounts',

if(Aggr(rank(-sum( [Actual Amount])),[Product ID])<= 3 ,'Average of TOP 3 Products by Actual Amounts'))

Measured Expression:

Avg(Risk Value)

Avg(Return Value)

Thanks in Advance,

-Vidya

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Multiple Dimension aggregation Columns

So basically:

to have the first 3 products by sales amout:

Sum({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Sales Amount])

Capture.PNG

to have the first 3 products by actual amount:

Sum({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Actual Amount])

Capture.PNG

so :

to have the avg of risk value depends on the top 3 based on sales amount:

Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])


Capture.PNG

to have the avg of risk value depends on the top 3 based on actual amount:

Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

Capture.PNG

if u want to have this in a table:

as dimension:

=ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')

as a measure:

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Sales Amounts', Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Actual Amounts',Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

))

result:

Capture.PNG

4 Replies
OmarBenSalem
Esteemed Contributor

Re: Multiple Dimension aggregation Columns

So basically:

to have the first 3 products by sales amout:

Sum({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Sales Amount])

Capture.PNG

to have the first 3 products by actual amount:

Sum({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Actual Amount])

Capture.PNG

so :

to have the avg of risk value depends on the top 3 based on sales amount:

Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])


Capture.PNG

to have the avg of risk value depends on the top 3 based on actual amount:

Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

Capture.PNG

if u want to have this in a table:

as dimension:

=ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')

as a measure:

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Sales Amounts', Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value]),

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Actual Amounts',Avg({<Product={"=rank(sum([Actual Amount]),Product)<=3"}>}[Risk Value])

))

result:

Capture.PNG

vidyasagar159
Contributor II

Re: Multiple Dimension aggregation Columns

Hi Omar,

You are really a genius. I really appreciate your help today. The expression you shared worked as expected. One last thing i would like to share is. I also want to include my own products example: If(wildmatch([Product],'*Micro*'),[Product]). Is there a way that I can add this if condition to the valuelist?

Thanks,

-Vidya

OmarBenSalem
Esteemed Contributor

Re: Multiple Dimension aggregation Columns

If I fully understand ur request:


Let's say you have 1000 products, but only 50 that contains Micro, and u want to to do the Avg for the top 3 of these 50?

Is that so?

If yes, u change ur expression from:

Avg({<Product={"=rank(sum([Sales Amount]),Product)<=3"}>}[Risk Value])


to

Avg({<Product={"=rank(sum(  {<Product={=wildmatch([Product],'*Micro*')=1}>}  [Sales Amount]),Product)<=3"}>}[Risk Value])


so: the final expressions would be:

as dimension:

=ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts')


as a measure:

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Sales Amounts',

Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Sales Amount]),Product)<=3"}>}[Risk Value]),

if(ValueList('Average of TOP 3 Products by Sales Amounts','Average of TOP 3 Products by Actual Amounts') =

'Average of TOP 3 Products by Actual Amounts',

Avg({<Product={"=rank(sum({<Product={=wildmatch([Product],'*Micro*')=1}>} [Actual Amount]),Product)<=3"}>}[Risk Value])

))

vidyasagar159
Contributor II

Re: Multiple Dimension aggregation Columns

Hi Omar,


I found the solution for this. let's follow the order.

  • I have 1000 Products, But only 50 that contains Micro. Now I want to show Avg risk value of top 3 by sales amount of 1000 Products and individual Riks values of products that contains Micro.

Example:

Capture.PNG

      Micro1                                                                                                                    10.20

      Mirco2                                                                                                                    12.36

  • Solution to achieve this requirement is.

load * inline [

Dim

1

2

3

4

5

6

7

8

9

10

];

Dimension:

=pick(Dim,'Average of TOP N Products by AUM: 1 YR Risk and Returns','Average of TOP N Products by 1 YR Flows: 1 YR Risk and Returns','Average of Bottom N Products by 1 YR Flows: 1 YR Risk and Returns',

if(WildMatch([Firm Name],'*Mirco*'),[Product Name]))

Measure:

=

PICK(Dim, Avg({<[Product ID]={"=rank(sum([Sales Amount]),[Product ID])<=10"}>}[Risk Value]),

          Avg({<[Product ID]={"=rank(sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),

          Avg({<[Product ID]={"=rank(-sum([Total Amount]),[Product ID])<=10"}>}[Risk Value]),

([Risk Value])

)

Thanks,

-Vidya

Community Browser