Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need your help and advice. I would like to add a column dimension in a table in Qliksense that show Grade as you can see om expect result.
ID, NO, Grade, Value
1 1 A 11
1 2 B 30
1 3 C 30
2 1 D 40
2 3 C 10
2 2 B 40
3 1 A 10
4 1 B 10
This is my expect result.
ID Max Count No Grade
1 30 3 2 B
2 40 3 1 D
3 10 1 1 A
4 10 1 1 B
I try to use this expression when I add a column dimension but I get "Invalid Dimension"
min(if(Value=aggr(max(Total <ID> Value),ID,No),Grade))
I also try to use
Aggr(min(if(Value=aggr(max(Total <ID> Value),ID,No),Grade)) ,Grade) but it doesn't work.
Regards
Nudjaree
Hi @nudjaree1
Try like below
LOAD *, RowNo() as Row INLINE [
ID, NO, Grade, Value
1,1,A,11
1,2,B,30
1 ,3 , C, 30
2, 1, D, 40
2, 3, C, 10
2, 2, B, 40
3, 1, A, 10
4, 1, B, 10
];
Dim / Measure
=Aggr(Only({<Row = {$(=Concat(Aggr(Min(Aggr(if(Value=max(Total <ID> Value),Row),ID, NO)), ID),','))}>}Grade), ID, NO)
@MayilVahanan It works. 🙂 Thank you very much.
May be something like this
=if(if(Value=MAx(total <ID>Value),aggr(min(NO),ID,Value)),Grade)
might be you are adding as dimension. You should add as measure not dimension.
Thank you for your answer but I get "-" when I create measure with this expression.
Yes, I added measure as well but I dont have the correct expression.
Hi @nudjaree1
Try like below
LOAD *, RowNo() as Row INLINE [
ID, NO, Grade, Value
1,1,A,11
1,2,B,30
1 ,3 , C, 30
2, 1, D, 40
2, 3, C, 10
2, 2, B, 40
3, 1, A, 10
4, 1, B, 10
];
Dim / Measure
=Aggr(Only({<Row = {$(=Concat(Aggr(Min(Aggr(if(Value=max(Total <ID> Value),Row),ID, NO)), ID),','))}>}Grade), ID, NO)
@MayilVahanan It works. 🙂 Thank you very much.
Hi @nudjaree1
Happy to help you.
can you mark as correct answer if its helps for you.