# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner

## Pick or Match using a Variable to Dimension Value to record averages

Hi,

I am presently loaded a table via script which has ID , Band and Band Value . Something like this :

 Model ID band band value AB1 0.2 1.178 AB2 0.25 2.364 AB1 0.25 -1.39 AB2 0.2 0.009 AB1 0.75 -2.333 AB2 0.75 0

I have also created a variable(not in script), where when user inputs certain value, the variable will calculate and give value as in band.

My problem is : Let say the variable after user input returns the value of 0.25.

How do i match 0.25 in the above table , to bring the band value of 0.25 which is 1.178. I want to show this in a table.

I tried using below formulas but getting no results and no error as well :

if(band = \$vBandCal, Bandvalue)

if(match(band, \$vBandCal, avg(Bandvalue))

Please let me know where am i going wrong?

Labels (1)
• ### Variables

1 Solution

Accepted Solutions
Contributor III

## Re: Pick or Match using a Variable to Dimension Value to record averages

Correction
avg(if(match(band, \$vBandCal),Bandvalue))
4 Replies
Contributor III

## Re: Pick or Match using a Variable to Dimension Value to record averages

I am not sure if I understand everything, but if you want to filter within aggregation, this should be the way

avg(if(match(band, \$vBandCal, Bandvalue),Bandvalue))

It will be better if you share more details about input and output..

For example I see two model with same band(0.75), what do you want to show in that case

Partner

## Re: Pick or Match using a Variable to Dimension Value to record averages

Hi, Thanks!

There are 2 basically 2 models with model IDs where band is same across all the model but only band values differ.

The variable \$Band Cal calculates the co-eff using user input which is on the fly and is equal to the dimension band.

I tried using the suggested solution but it gives out of memory error.

Basically the output would look like this :

 Model ID \$Band Cal Band Value AB1 0.25 -1.39 AB2 0.75 0
Contributor III

## Re: Pick or Match using a Variable to Dimension Value to record averages

Correction
avg(if(match(band, \$vBandCal),Bandvalue))
Partner

## Re: Pick or Match using a Variable to Dimension Value to record averages

Hi, the problem is with variable declaration. I fixed that and it worked using below formula!. Thanks!