topic Re: average vs single value in App Development

average vs single value

Mon, 13 Jun 2016 15:07:51 GMT

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

sunny_talwar — Mon, 13 Jun 2016 15:16:17 GMT

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

Mon, 13 Jun 2016 16:20:51 GMT

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

sunny_talwar — Mon, 13 Jun 2016 16:28:29 GMT

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

Re: average vs single value

Wed, 15 Jun 2016 12:24:53 GMT

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

sunny_talwar — Wed, 15 Jun 2016 16:25:51 GMT

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

Re: average vs single value

Thu, 16 Jun 2016 08:23:04 GMT

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

sunny_talwar — Thu, 16 Jun 2016 17:28:37 GMT

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

Tue, 21 Jun 2016 16:00:03 GMT

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

Re: average vs single value

sunny_talwar — Tue, 21 Jun 2016 21:10:38 GMT

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