topic Aggr function in QlikView

Aggr function

Wed, 25 Jan 2012 14:10:21 GMT

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

swuehl — Wed, 25 Jan 2012 14:21:30 GMT

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

Wed, 25 Jan 2012 15:07:49 GMT

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

swuehl — Wed, 25 Jan 2012 15:15:04 GMT

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

Wed, 25 Jan 2012 15:25:54 GMT

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

swuehl — Wed, 25 Jan 2012 15:46:46 GMT

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.

Aggr function

Wed, 25 Jan 2012 17:45:49 GMT

As you can see in the image, I just need to show models order by lowest CO2, showing related characteristics. Thanks for your help anyway

Davide