Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone ,
I have 3 dimensions and two expressions in my straight table ,
Category Sub-Category Item Sale Rank
A a a1 5 2
A a b1 6 1
A b a1 4 1
B a a1 4 1
B b a1 8 1
B b b1 7 2
C a a1 3 1
C b a1 5 1
C b b1 4 2
D a a1 3 1
D b a1 2 1
D c a1 3 1
Here sales are grouped by Category , Sub-Category and Item, What I want to show only top 1 sales within each subcategory (i.e. with Rank 1),
How can i do that , desired output is given below
Category Sub-Category Item Sale Rank
A a b1 6 1
A b a1 4 1
B a a1 4 1
B b a1 8 1
C a a1 3 1
C b a1 5 1
D a a1 3 1
D b a1 2 1
D c a1 3 1
Hi,
First try this:
2. And if not working then use
If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item))
3. Or
Only(If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item)))
Regards,
Sokkorn
Hi,
Aggr(Min(Rank),Item) for last expression should work.
Regards,
Sokkorn
Hi sokkorn ,
Of course I can filter data in an expression using above expression , but I dont want to add an extra expression I just want to show records with rank 1 and hide others within straight table (visually).
Hi mkhaliq13,
What is your Rank expression? Can you share me/
Rgds,
Sokkorn
Hi ,
Here is my rank expression .
Aggr(Rank($(Sale)),Category,Sub-Category ,Item)
Hi,
What about this
If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item),Null())
Regards,
Sokkorn
Well this outputs
Category Sub-Category Item Sale Rank
A a a1 5 -
A a b1 6 1
A b a1 4 1
B a a1 4 1
B b a1 8 1
B b b1 7 -
C a a1 3 1
C b a1 5 1
C b b1 4 -
D a a1 3 1
D b a1 2 1
D c a1 3 1
But I want these rows hidden in straight table like this
Category Sub-Category Item Sale Rank
A a b1 6 1
A b a1 4 1
B a a1 4 1
B b a1 8 1
C a a1 3 1
C b a1 5 1
D a a1 3 1
D b a1 2 1
D c a1 3 1
Hi,
First try this:
2. And if not working then use
If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item))
3. Or
Only(If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item)))
Regards,
Sokkorn
Hi ,
Well I have fixed this by following way ,
I have created a calculated dimension with following expression :
If(Aggr(Rank($(Sale)),Category,Sub-Category ,Item)=1,Aggr(Rank($(Sale)),Category,Sub-Category ,Item),Null())
then I checked Suppress when value is null. It gives me the desired output , thanks Sokkorn for your kind response.
Hi,
That great to hear this. Can you close this post?
Cheer!!!
Sokkorn