1 Reply Latest reply: Oct 23, 2014 12:14 AM by jagan mohan rao appala RSS

    How to remove inbuilt aggregation in group dimensions when creating charts?

      hi experts,

      I am still a newbie in qlikview and have a question for my situation:

      Data (in excel): 'empty' keyword literally exists in the below excel file

         WW   SL1                  SL2      SL3          Pass Fail

             1    TSL1               empty    empty       100    50               ----->aggregated for the whole TSL1

             1    TSL1               TSL2.0   empty        20    20                ------> aggregated for the whole TLS2.0

             1    TSL1               TSL2.0   TSL3.0      10     5  

             1    TSL1               TSL2.0   TSL3.1      10    15

             1    TSL1               TSL2.1   empty        50    30                 ------> aggregated for the whole TLS2.1

             1    TSL1               TSL2.1   TSL3.1       30    20

             1    TSL1               TSL2.1   TSL3.2       20    10

             1    TSL1               TSL2.2   empty         30      0               ------> (aggregated) for the whole TLS2.0

             2    TSL1               empty     empty       120    30

           ...............................................................

      My goal is that when displaying data:

      I do not want the aggregations to be part of the data in the charts. It should show a sum(non-aggregated rows).

       

      I tried 2 approaches:

      1. While loading data, i did a if(SL1<>'empty',SL1) as sub-lev1, etc. and i get a null instead of the empty.

      Then do a if(SL1<>null(),SL1) in the calculated dimension of the group drill down instead of simple SL1 - did not work.

       

      2. Import data with empty field.

       

      Now when presenting the data for group drill down, a simple sum(Passed) as expression will not do because at every level, the sublevel aggregation gets added. So i am not sure how to remove inbuilt sublevel aggregation.

      Any help in this scenario would be great.

      I tried doing an if (SL1<>empty,SL1) in the caclulated dimension of the group drill down instead of a simple SL1 - this did not help either.

       

      thanks

      Ramesh