6 Replies Latest reply: Jan 25, 2012 12:45 PM by falstaff73 RSS

    Aggr function

      Hello everyone, just development with QlikView.


      My need right now is to present some data related to the minimum value of one of them. Let me explain:


       

      I have a table of car models. Should I create a table (pivot better) Qlik featuring models who have the lowest CO2 models are identified, have all the other features of the vehicle (eg HP, capacity, consumption ...)


       

      Practically, with a calculated dimension, recovery models that have the lowest CO2:

       

      =if(aggr(Min(CO2_LEVEL_G_KM_COMBINED_HIGH),MODEL)>0,aggr(Min(CO2_LEVEL_G_KM_COMBINED_HIGH),MODEL),null())

       



       

      the other two dimensions are MODEL and Descr_Caratt


       

      What expression do I retrieve the values of the characteristics (Data_value is the field) associated with the model with the lowest CO2 I have in that particular column?


       

      I know for a fact that is simple, but I know very little about this tool, thanks a lot!


       

      David

        • Aggr function
          Stefan Wühl

          David,

           

          could you upload some lines of sample data as INLINE coded table or as small sample app? And your expected result? I think this would make things much clearer too me.

           

          Regards,

          Stefan

            • Aggr function

              Hi Stefan,

                   thanks for your reply. In attachemnt you can find a couple of screenshot of my qvw. Unfortunatelly the qlik document it is over 50 mb size, so I cannot upload this.

              By the way in the table above I need to show the data corresponding to lowest Co2, positioned at the top of the table. In the grid below there are real data to compare results.

              Instead of "a" I would show the correct value

               

              Davide

                • Aggr function
                  Stefan Wühl

                  Have you tried just putting in

                  =Data_value

                   

                  as expression? Or

                  =concat(Data_value, ' - ')

                   

                  just to check for unambiguous data. I think since you have Desc_Caratt and Model as dimensions, your Data_value should already be ready to pick.

                   

                  Regards,

                  Stefan

                    • Aggr function

                      It is the same think I though.

                      I tried with =Data_Value and the most of data is null ('-')

                      If I use Concat(data_value), qlik shows all data_value of that characteristic (very much) and not only the first one reflecting lowest CO2.

                       

                      Sob

                        • Aggr function
                          Stefan Wühl

                          Ok, let's step one step back.

                           

                          Your calculated dimension:

                          =if(

                          aggr(Min(CO2_LEVEL_G_KM_COMBINED_HIGH),MODEL)>0,

                          aggr(Min(CO2_LEVEL_G_KM_COMBINED_HIGH),MODEL),

                          null()

                          )

                           

                          what do you want this to achieve? I believe you are getting a value back per MODEL, but not filter your Models for the one with the lowest CO2 level.

                          Try maybe

                          = aggr( if( Min(total CO2_LEVEL_G_KM_COMBINED_HIGH) = CO2_LEVEL_G_KM_COMBINED_HIGH,MODEL), MODEL)

                           

                           

                          This won't explain why you get a lot of Data_Values in your expression, since I assume your data should be limited by MODEL and Desc_Caratt anyway.

                          Seems like I still misunderstand what you are doing.