Above() Function On Different Dimension (for chart showing % change since last data point)

jjwild00 — Tue, 09 Dec 2014 01:34:45 GMT

I have a line graph that has two dimensions:

- Week Ending

- Type of user device used to access a web site (iPhone, iPad, Desktop PC, etc).

The expression is the number of logins.

They also want a Week Over Week graph where the value represents the percentage change in logins since the prior week.

So I have to have an expression that compares the current week and the prior week.

I thought I could use above() to access the prior week's data.

Unfortunately, the chart has to have the week ending dates as the first sort field so the week ending dates can be on the x axis. This means that above() gives me the value for the prior user device for the same week, not the prior week for the same user device.

I thought about using above(expression, offset) where offset was a variable representing the number of user devices in the currently selected data. If all devices showed at all times in the chart, this would work. However, they don't all always show.

Anyone have any ideas on this?

Re: Above() Function On Different Dimension (for chart showing % change since last data point)

jjwild00 — Thu, 11 Dec 2014 04:06:46 GMT

I looked around some more on the site and it looks like others are trying to find the same solution that I am.

So I looked harder at this.

I realized that I could calculate how many rows to climb up the chart to get to the prior week's value for the current user device.

There are three factors in the solution I used.

The first is a variable that contains the currently selected field in the cycle group on the chart. (The cycle group part of the problem was not mentioned by me in the initial post.)

vstrCurrentField =GetCurrentField([Platforms])

The next sets a variable to the number of these items in the currently selected data.

vintCountLoginPlatforms =count({<RecordType={'Login'}>} DISTINCT [$(vstrCurrentField)])

There are three important points here:

1. You need to have the fieldname from the cycle group in a variable. DISTINCT [GetCurrentField(Platforms)] would not work. QV does not know to evaluate the function in that location. There is a more general issue as well, that GetCurrentField(Platforms) returns a text value. I have had situations where I needed this value treated as a field. If you put the result of GetCurrentField() into a variable and then use the variable, QV recognizes it now as a fieldname.

2. You need to have the number of platforms in a variable. You cannot put this count expression inside the expression in the chart. This is so it looks at all selected data to do the calculation, not just the slice passed to the expression.

3. The expression needs to contain the same set analysis expression as is used in the chart.

The final piece is the expression in the chart:

above(total count(DISTINCT _Customer) , $(vintCountLoginPlatforms))

John Wildanger

topic Re: Above() Function On Different Dimension (for chart showing % change since last data point) in QlikView

Above() Function On Different Dimension (for chart showing % change since last data point)

Re: Above() Function On Different Dimension (for chart showing % change since last data point)