8 Replies Latest reply: Oct 24, 2016 12:22 PM by Elina Vergara

%Mix

Elina Vergara Oct 21, 2016 4:30 PM

Hello, i have a pivot table where i need to present %mix, but i have a dimensión Region , some like this, my problem is when i use the expression Sum(TOTAL Aggr(sum({<$(vSetYearAct),$(TipRegion)>}#Sales),CanalName)) for get sum of sales for region show sales for noth and south, not separate

Region	Nort		South
Canal Name	Sales	%Mix	Sales	%Mix
Canal 1	5472	33%	8712	52%
Canal 2	8769	52%	6478	39%
Canal 3	2547	15%	2145	13%
Total	16788	100%	17335	103%

Re: %Mix

Oleg Troyansky Oct 21, 2016 4:42 PM (in response to Elina Vergara)

Hi Elina,

I don't think you really need the AGGR function for this calculation. I'd use the following:

SUM({<condition>} Sales)/sum({<condition>} Total Sales)

cheers,
Oleg Troyansky
Learn AGGR, Set Analysis, and many other advanced techniques from my book QlikView Your Business.
Like Show 0 Likes (0)

Actions
- Re: %Mix
  
  Elina Vergara Oct 21, 2016 5:30 PM (in response to Oleg Troyansky )
  
  I've already tried that option, but I don't have result. It could be because I have canal and region as dimension??
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: %Mix
    
    John Witherspoon Oct 21, 2016 7:20 PM (in response to Elina Vergara)
    
    It looks like you want the %Mix within the Region? Just need to add <Region> after the total in Oleg's expression, I think.
    
    sum({<$(vSetYearAct),$(TipRegion)>} #Sales)
    /sum({<$(vSetYearAct),$(TipRegion)>} total <Region> #Sales)
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: %Mix
      
      Elina Vergara Oct 21, 2016 11:18 PM (in response to John Witherspoon )
      
      sorry, i don´t explain correctly, i have two dimensions. canal and regional, and i have two variables vSetYearAct and TipRegion, the last save if region is north or south, i tried your expression, but do not work.
      
      (sum({<$(vSetYearAct),$(TipRegion)>}TOTAL <Regional>} #Sales)
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: %Mix
        
        Oleg Troyansky Oct 22, 2016 11:08 AM (in response to Elina Vergara)
        
        Hi Elina,
        
        it would be a lot easier if you could create a small example and share it. We could then review it and help you find the solution.
        
        cheers,
        Oleg Troyansky
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: %Mix
        
        Elina Vergara Oct 23, 2016 3:01 AM (in response to Oleg Troyansky )
        
        thannks Oleg, attach file with examples, thanks for your help
        
        Prueba.qvw 155.0 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: %Mix
        
        Oleg Troyansky Oct 24, 2016 10:52 AM (in response to Elina Vergara)
        
        !
        Hi Elina,
        
        you simply had an extra bracket } after the TOTAL qualifier. Attached is the corrected formula.
        
        cheers,
        
        Oleg Troyansky
        Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business
        
        Prueba.qvw 154.5 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: %Mix
        
        Elina Vergara Oct 24, 2016 12:22 PM (in response to Oleg Troyansky )
        
        Thanks, for your time
        
        Like Show 1 Like (1)
        
        Actions

Go to original post