Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
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 IDbandband value
AB10.21.178
AB20.252.364
AB10.25-1.39
AB20.20.009
AB10.75-2.333
AB20.750

 

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)
1 Solution

Accepted Solutions
asinha1991
Contributor III

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

Correction
avg(if(match(band, $vBandCal),Bandvalue))
4 Replies
asinha1991
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
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 CalBand Value
AB10.25-1.39
AB20.750
asinha1991
Contributor III

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

Correction
avg(if(match(band, $vBandCal),Bandvalue))
Partner
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!