15 Replies Latest reply: Dec 4, 2017 10:00 AM by Sunny Talwar

how highest value of Dimension A in Dimension B

Max Maudin Nov 16, 2017 4:15 AM

Hi all

I have 2 dimensions: the first is Dimension "Problem" and the 2nd "Reaction". The Goal is to Show the highest value (Count) of Problem in the Dimension "Reaction". For example the most often Problem of customers is A

The Pie Chart "Reaction" must Show the different reactions of the customers with Problem A.

Atm I only get the highest value of the Problem by

max(aggr(count({$<

Source={'V2}>}#),Problem))

But I need the Dimension - not the value. Do you have any idea how i could works?

Thanks,

Max

Re: how highest value of Dimension A in Dimension B

Devarasu R Nov 16, 2017 11:48 AM (in response to Max Maudin)

Hi,
Can you try like this
FirstSortedValue(DISTINCT Problem,-aggr(count({$<Source={'V2}>}#),Problem))
Like Show 1 Like (1)

Actions
- Re: how highest value of Dimension A in Dimension B
  
  Max Maudin Nov 17, 2017 5:24 AM (in response to Devarasu R)
  
  Hi,
  
  Thanks for ur advice, but it still doesnt' work (No value appears). Im not sure if this Expression goes to the rigth direction.
  
  Point is that the highest value of "Problem" can Change -->depending on a different selection. For example: If I choose the product "Loan" the highest value of Problem is Service, but for product "Card" the most current Problem is "billing".
  So if I choose "Loan" as Product then in the Dimension "Problem" the most current value is "Service". Now in Dimension "Reaction" there must come all reactions for the "Service" automatically.
  
  Is it even possible or are there too many variables?
  
  Thanks
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: how highest value of Dimension A in Dimension B
    
    Sunny Talwar Nov 17, 2017 8:35 AM (in response to Max Maudin)
    
    May be because of a missing single quote?
    
    FirstSortedValue(DISTINCT Problem, -Aggr(Count({$<Source = {'V2'}>} #), Problem))
    
    Like Show 1 Like (1)
    
    Actions
    - Re: how highest value of Dimension A in Dimension B
      
      Max Maudin Nov 17, 2017 8:45 AM (in response to Sunny Talwar )
      
      No, I already found this missing Quote..
      
      Like Show 1 Like (1)
      
      Actions
      - Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Nov 17, 2017 11:19 AM (in response to Max Maudin)
        
        Would you be able to share a sample to see the issue?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Max Maudin Nov 21, 2017 3:27 AM (in response to Sunny Talwar )
        
        Hi Sunny,
        Thanks for your Patience. Below an example for you
        
        Problem Value
        Creditcard 20
        Loan 10
        Other A 5
        Other B 1
        Other C
        3
        
        Problem Reaction Value
        Creditcard Angry 20
        Creditcard Termination 40
        Creditcard Prod Change 10
        Loan Angry 5
        Loan Termination 10
        Loan Desperately 45
        Loan Other 2
        Other A X 4
        Other A Angry 12
        Other B Z 14
        Other C Angry 19
        Other C H 21
        
        For this tables the most often Problem ist Creditcard. Now I have 2 pie Charts. The first pie Chart Shows me the Distribution of the 3 mosten current Problems (this works already). The Second Chart has the Dimension Reaction. The Goal is now that the 2nd Charts Shows (without clicking) only the reactions from Creditcard (because Creditcard is the most often Problem). So the 2nd chard would Show me Angry=20, Termination=40 and Prod Change=10
        
        Next step would be (if possible) a 3rd Chart which is showing me the Reactions of the Problem with the 2nd larges majority - in this case Loan.
        
        Does it help you?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Nov 21, 2017 4:40 AM (in response to Max Maudin)
        
        May be this
        
        2nd chart
        Sum({<Problem = {"$(=FirstSortedValue(DISTINCT Problem, -Aggr(Count({$<Source = {'V2'}>} #), Problem)))"}>}Value)
        
        3rd chart
        Sum({<Problem = {"$(=FirstSortedValue(DISTINCT Problem, -Aggr(Count({$<Source = {'V2'}>} #), Problem), 2))"}>}Value)
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Max Maudin Nov 21, 2017 5:29 AM (in response to Sunny Talwar )
        
        It doesn't recognize the Expression after
        "Sum({<Problem ="
        
        So i tried
        Sum({$<Problem= {$(=FirstSortedValue(DISTINCT Problem, -Aggr(Count({$<Source= {'V2'}>} #), Problem)))}>}#)
        It doesn't recognize the Expression after Sum({$<Problem = {-->the "$" is on a wrong place.
        
        If I take it out, the formula is ok but no value appears
        Sum({$<Problem={'$(=FirstSortedValue(DISTINCT Problem, -Aggr(Count({$<Source= {'V2'}>} #), Problem)))'}>}#)
        
        Do i missunderstand some Expression values?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Nov 21, 2017 7:01 AM (in response to Max Maudin)
        
        It doesn't recognize? What do you mean? Have you tried clicking okay to see if it gives the right result or not?
        
        Like Show 1 Like (1)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Max Maudin Nov 23, 2017 10:16 AM (in response to Sunny Talwar )
        
        Hi,
        
        Sry for the delayed answer. Not recognize means that qlik sense doesn't Show the Expression as fine (e.g. the "$" is normaly shown blue when the Expression is fine, but not in this Expression). You know what I mean?
        So I think there are some missing quotes in the Expression, but im not sure..
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Nov 23, 2017 5:58 PM (in response to Max Maudin)
        
        That is a misconception that you might have... I would suggest you to try the expression and click okay to see if you get what you need... just because syntax editor is not showing right colors, doesn't really mean that expression is incorrect .
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Max Maudin Dec 4, 2017 9:28 AM (in response to Sunny Talwar )
        
        Hi Sunny,
        With an Expression it still doesnt work , but I do it now manually by clicking the most current Problem instead to write it in a Expression.
        
        Anyway, Thanks for your help!
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Dec 4, 2017 9:38 AM (in response to Max Maudin)
        
        Would you be able to share a sample?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Max Maudin Dec 4, 2017 9:52 AM (in response to Sunny Talwar )
        
        Unfortunately I can't upload Pictures or something else from my Company Computer but It's simple: Just 2 Pie Chart- 1 Pie Chart Shows "Problem" other "Reaction". So most often Problem is for example Credit Card. So I click on credit Card in the pie Chart and then it shows me all reactions to this Topic. So instead that this process goes by an Expression I have to click myself.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: how highest value of Dimension A in Dimension B
        
        Sunny Talwar Dec 4, 2017 10:00 AM (in response to Max Maudin)
        
        Not sure my friend... may be someone else can offer help
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post

Problem	Value
Creditcard	20
Loan	10
Other A	5
Other B	1
Other C	3

Problem	Reaction	Value
Creditcard	Angry	20
Creditcard	Termination	40
Creditcard	Prod Change	10
Loan	Angry	5
Loan	Termination	10
Loan	Desperately	45
Loan	Other	2
Other A	X	4
Other A	Angry	12
Other B	Z	14
Other C	Angry	19
Other C	H	21