topic Re: Straight Table - Comparison with Baseline in QlikView

Straight Table - Comparison with Baseline

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a Qlikview problem that I need to solve.

I have a table1 with has a pagename, date, position and pageviews.

I have another table2 which is the baseline table which has the baselineposition, avg.pageviews

Now I want to create a straight table which has the pagename, avg(position) as int, avg(pageviews) [all from table1] and then get the baseline avg. pageviews from table2 corresponding to avg(position) calculated from table1.

Is this possible?

Bharath.

Update: Added sample data and desired result straight table. hope this helps.

Message was edited by: bharath radhakrishnan

The solution provided by Oscar shows the correct number but the expression is blank when I open the dashboard in qlikview or in the web. It shows the numbers only when I refresh the dashboard. Any help is greatly appreciated. Attached the qlikview and source data excel files. Hope this helps.

Update2:

Found the solution!!!

I dont know why...but the issue seems to be with the FieldIndex function and not the FieldValue function.

So this is what I did.

In my query I sorted the baseline table by baseline position (ascending) and then I changed my calcuation to

FieldValue( 'baselineimpressions', Round( Avg( position ) ) )

This worked all the time and also showed the values when I accessed it in Access Point through my browser.

P.S.: I am still curious to find out why the FieldIndex didnt work with a calculation. It worked when I just hardcored an integer like FieldValue( 'baselineimpressions', FieldIndex( 'baselineposition', 1) )

Thanks to Oscar for providing the initial solution.

Re: Straight Table - Comparison with Baseline

albertovarela — Thu, 08 Jun 2017 17:47:13 GMT

Can you share some sample data? and an example of the desired outcome?

Re: Straight Table - Comparison with Baseline

oscar_ortiz — Thu, 08 Jun 2017 17:52:33 GMT

It sounds like something that you could accomplish using alternate states. Can you send a screen shot of what you are looking to accomplish?

Re: Straight Table - Comparison with Baseline

albertovarela — Thu, 08 Jun 2017 20:13:59 GMT

Please find attached a qvw.

I hope it helps.

Cheers!

Re: Straight Table - Comparison with Baseline

Anonymous — Thu, 08 Jun 2017 20:23:44 GMT

Thanks for your reply. However I dont want to calculate the %avg position in the query, as there could be more dimensions in the future. I am trying to see if there is a solution where the tables are not linked and still do what I want to do. Am I being clear?

Re: Straight Table - Comparison with Baseline

oscar_ortiz — Thu, 08 Jun 2017 20:57:03 GMT

I believe this is what you are trying to accomplish.

Re: Straight Table - Comparison with Baseline

Anonymous — Fri, 09 Jun 2017 15:04:44 GMT

Thank you Oscar...

YOU ARE THE MAN!!!!

Re: Straight Table - Comparison with Baseline

Anonymous — Fri, 09 Jun 2017 15:59:52 GMT

Oscar,

One small issue though. The values dont show up unless I reload the dashboard. Also when I open it in the browser the field is blank.

Should I do anything to make it show up all the time?

Re: Straight Table - Comparison with Baseline

Anonymous — Mon, 12 Jun 2017 17:09:16 GMT

Found the solution!!!

I dont know why...but the issue seems to be with the FieldIndex function and not the FieldValue function.

So this is what I did.

In my query I sorted the baseline table by baseline position (ascending) and then I changed my calcuation to

FieldValue( 'baselineimpressions', Round( Avg( position ) ) )

This worked all the time and also showed the values when I accessed it in Access Point through my browser.

Thanks to Oscar for providing the initial solution.

Re: Straight Table - Comparison with Baseline

oscar_ortiz — Mon, 12 Jun 2017 17:54:26 GMT

Sorry for not getting back to you sooner, Friday's are travel days and I usually try to stay away from work type issues on the weekends.

So the FieldIndex should read the position of the field value by load order. In the example that I provided QlikView read the second table in load order and everything was fine. Based on the expression for rounding the Average position I returned the proper value and everything worked out. You may want to check the results of the calculation to make sure it is returning an integer that will match your baseline position field.

From Help:

FieldIndex(fieldname , value )

Returns the position of the field value value in the field fieldname (by load order). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string value, e.g. the field name must be enclosed by single quotes.