7 Replies Latest reply: Apr 10, 2017 9:26 PM by Sunny Talwar

Strange pivot table behavior

Aleksandrs Jemeljanovs Apr 10, 2017 4:50 AM

Greetings to the community!

I struggle with the following Pivot table behavior:

Screenshot 101: Order Month-Project-Product, the TOTAL<Month> gives me the right values;

Screenshot 102: Order Product-Month-Project, the TOTAL<Month> gives the right values when all the levels are open,

however

Screenshot 103: same order, but when I close one of the drills (part. 'Door'), I get wrong total values.

What is wrong here - either it's me and a lack of understanding of Qlik features or is it a bug?

I've attached the the app with this table.

103.PNG 43.4 K
102.PNG 48.2 K
101.PNG 44.8 K
PivotBehavior.qvf 176.0 K

Re: Strange pivot table behavior

Sunny Talwar Apr 7, 2017 11:56 AM (in response to Aleksandrs Jemeljanovs)

What is the total you are expecting to see? I am trying to understand that bit
Like Show 1 Like (1)

Actions
- Re: Strange pivot table behavior
  
  Aleksandrs Jemeljanovs Apr 10, 2017 4:29 AM (in response to Sunny Talwar )
  
  I want to get the 101 and 102 slide TOTAL <Month> disregarding the selections and drill-down states, like the ones I get in Screenshot 101 and 102, so basically it should show me the total costs per month in any of view combinations.
  It would be the total cost of specific product per month.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Strange pivot table behavior
    
    Sunny Talwar Apr 10, 2017 6:08 AM (in response to Aleksandrs Jemeljanovs)
    
    May be this
    
    If(Dimensionality() = 3, Aggr(nodistinct Sum(Cost), Month), Sum(TOTAL <Month> Cost))
    
    PivotBehavior.qvf 176.0 K
    
    Like Show 1 Like (1)
    
    Actions
    - Re: Strange pivot table behavior
      
      Aleksandrs Jemeljanovs Apr 10, 2017 10:21 AM (in response to Sunny Talwar )
      
      Nice! It works when the Month is at the level 3, however, when I move the month to the second level, it breaks. Maybe You know the trick?
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Strange pivot table behavior
        
        Sunny Talwar Apr 10, 2017 11:22 AM (in response to Aleksandrs Jemeljanovs)
        
        Month is at the second level here, what's wrong?
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Strange pivot table behavior
        
        Alex Timofeyev Apr 10, 2017 5:37 PM (in response to Sunny Talwar )
        
        Sunny,
        
        Look at the value for "January" on the second level under "Door". It's 305, but it's supposed to be 420, just like the levels below.
        
        Like Show 2 Likes (2)
        
        Actions
        
        Re: Strange pivot table behavior
        
        Sunny Talwar Apr 10, 2017 9:26 PM (in response to Alex Timofeyev )
        
        I see what you mean..... now.... I will take a look at this and
        
        Like Show 2 Likes (2)
        
        Actions

Go to original post