0 Replies Latest reply: Oct 22, 2012 4:32 AM by Matan Zittoun RSS

    Smallest vTopN Zero/Null Supression problem in Bar Chart

      Hi QlikView Community
      it's my first time asking questions here so I'll try to make it as brief and clear as possible.

       

      up unril SR 10 I could create a bar chart object, and limit the dimention to show only the Smallest vTopN while supressing Nulls and Zeros.
      unfortunatelly, since moving to SR 11 something malfunctioned and is does not supress those values anymore thus showing ' NO Data'

       

      I have a Group-Cycle dimention of Product and Product Family and I with the bar chart to show an elaborate expression such as this one:

      =

       

       

      if(fabs(count(GetFieldSelections(CustomerName))) > 0 or fabs(count(GetFieldSelections(CenterCustomerName))) > 0 ,0&'%',

       
      if(Year= year(today()),sum(RetailPrice)/

      if(Dimensionality()=0 or Dimensionality()=1 or Dimensionality()=2,

        
      if(Dimensionality()=1 or Dimensionality()=0 ,

          
      if (fabs(isnull(GetFieldSelections(Month))= 0), sum(Target_Family)*vWorkdatetilltoday/vWorkdatetillmonth + sum(TargetType)*vWorkdatetilltoday/vWorkdatetillmonth,

          
      sum({$<Month = {$(vCurrentMonth)} >}Target_Family*($(vWorkdaysTillToday)-1)/$(vWorkDaysThisMonth)) + sum({$<Month = {$(vCurrentMonth)} >}TargetType*($(vWorkdaysTillToday)-1)/$(vWorkDaysThisMonth))

      +
      sum({$<Year={$(vCurrentYear)},Month ={"<$(vCurrentMonth)"}>} Target_Family)+sum({$<Year={$(vCurrentYear)},Month ={"<$(vCurrentMonth)"}>} TargetType)),



        
      if(Dimensionality()=2,

          
      if (fabs(isnull(GetFieldSelections(Month))= 0), sum(Target_Family)*vWorkdatetilltoday/vWorkdatetillmonth,

          
      sum({$<Month = {$(vCurrentMonth)} >}Target_Family*($(vWorkdaysTillToday)-1)/$(vWorkDaysThisMonth))

           +
      sum({$<Year={$(vCurrentYear)},Month ={"<$(vCurrentMonth)"}>} Target_Family)),

          

        
      if(Dimensionality()=3,

          
      if (fabs(isnull(GetFieldSelections(Month))= 0),,)

          )))

      )

      )

      )

      I'm to calculate Goal Covarge %, the expression is taken form what I've written in a pivot table showing the details based on Dimentionality. some containing product goals and other only product family goals.

      HOWEVER, while this works for Biggest vTopN, it does not work for Smallest for the reason I explained before

       

      I've tried set Analyisis and Calculated dimentions, no Luck there.

       

      any Ideas?

       

      much appreciated!

       

      Matan.