Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator
Creator

Grouping the Dimension Values Based on Metric Value in Straight table

Hi Team,

Could you please help me in grouping the dimension values in the Straight table. Below is the table that I have and I need Sales Rep concat if the Count metric is the same. The expected result is also shown below

RankSales RepCount
1Rep110
2Rep29
3Rep38
4Rep48
4Rep57
5Rep67
6Rep77
5Rep8, 6
6Rep96
7Rep106
8Rep156

 

Expected Result:

RankSales RepCount
1Rep110
2Rep29
3Rep3, Rep4 8
4Rep5, Rep6, Rep77
5Rep8, Rep9, Rep10, Rep156
Labels (3)
1 Solution

Accepted Solutions
Kushal_Chawda

@polisetti  You need to swap the position of dimension and measure

Dimension:
=aggr(sum(Count),[Sales Rep])
//Replace sum(Count) with your actual count expression

 

Measures:

1) Sales Rep
=Concat([Sales Rep],',')

2) Rank
=RowNo(Total)

 

Note:

Make a sort order of count measure  as descending and promote it to first.

Screenshot 2020-09-25 124834.png

View solution in original post

7 Replies
Kushal_Chawda

@polisetti  Is the Rank is your dimension or measure?

polisetti
Creator
Creator
Author

@Kushal_Chawda Rank is the Measure calculated based on Count of Accounts. 

Kushal_Chawda

@polisetti  You need to swap the position of dimension and measure

Dimension:
=aggr(sum(Count),[Sales Rep])
//Replace sum(Count) with your actual count expression

 

Measures:

1) Sales Rep
=Concat([Sales Rep],',')

2) Rank
=RowNo(Total)

 

Note:

Make a sort order of count measure  as descending and promote it to first.

Screenshot 2020-09-25 124834.png

polisetti
Creator
Creator
Author

@Kushal_Chawda On the dimension - to replace the expression. The actual count expression contains set analysis like below which will result to null.

=aggr(Count(Distinct{<[Source]={'Sales'},[Date]={">$(=Date(Today()-90))<=$(=Date(Today()))"}>} AccountId),[Rep Name])

How to do that?

polisetti
Creator
Creator
Author

@Kushal_Chawda I think this is working. I need to change the things based on my requirement. 

Thank you

sunny_talwar

@polisetti the calculated dimension you posted resulted in null? Or do you just think it will result in null? I think all you need is to add Rank() function to get a rank here

=Aggr(
  Rank(Count(Distinct{<[Source]={'Sales'},[Date]={">$(=Date(Today()-90))<=$(=Date(Today()))"}>} AccountId))
, [Rep Name])
polisetti
Creator
Creator
Author

@sunny_talwar I thought it will result null but @Kushal_Chawda  logic worked. Thanks again