average vs single value

[#1]

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)

[sunny_talwar]

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)

[#3]

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

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

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

[#7]

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

[sunny_talwar]

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), ', ')

[#9]

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

[sunny_talwar]

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