9 Replies Latest reply: Jun 21, 2016 5:10 PM by Sunny Talwar

# average vs single value

Hi, i want to compare database single values (amount) with the average value of every shop and if the value is lower i want to obtain the id_moviment, but a line like this doesn't work,

if(amount < avg(aggr (amount, shop_id_movimento)), id_moviment)

• ###### Re: average vs single value

I am guessing that this is used a calculated dimension. Can you try this:

If(amount < Avg(TOTAL Aggr(amount, shop_id_movimento)), id_moviment)

• ###### Re: average vs single value

It doesn't work, maybe because it take the first 'amount' from all the table, instead i want that qlik compare only the value of the shop with the average of all the values of shop

• ###### Re: average vs single value

Would you be able to provide more information as to where exactly are you trying to use this?

• ###### Re: average vs single value

I have a database with transactions of many shops and i want find the transactions that are above or under average of the transactions of every single shop

• ###### Re: average vs single value

Would you be able to share few rows of data with the expected output to help you better?

• ###### Re: average vs single value

input:

id_moviment = id of every transaction

Amount = amount \$ of every transaction

shop_id_movimento =  id of every shop

(id_moviment, Amount, shop_id_movimento)

(3098465, 10€, 56)

(2049464, 90€, 56)

(2064564, 100€, 56)

(2054651, 12€, 80)

(405656, 20€, 80)

(504650, 2€, 80)

avg shop: 56 = 66,66€

avg shop: 80 = 11,33€

output:

id_moviment = 3098465

id_moviment = 504650

id_moviment: of every transaction with an amount that is lower than the average amount for every shop

• ###### Re: average vs single value

I don't know how you are visualizing this, but this is what you can try:

Dimension:

shop_id_movimento,

id_moviment

Expression:

=If(Amount < Avg(TOTAL <shop_id_movimento> Amount), id_moviment)

or this if you only have shop_id_movimento as dimension or if you are using a KPI Bar

=Concat(DISTINCT Aggr(If(Amount < Avg(TOTAL <shop_id_movimento> Amount), id_moviment), id_moviment, shop_id_movimento), ', ')

• ###### Re: average vs single value

Great thanks!! it works! not so beautiful in table visualization but it works

• ###### Re: average vs single value

Great, if you are satisfied with what you have, please close this thread down by marking the correct and helpful response. If you still looking to make improvements, please elaborate on how you want the visualization to be improved.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny