topic Above function for two dimensions in line chart in QlikView

Above function for two dimensions in line chart

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

Hi everyone,

We need to show Period Over Period calculation in line chart which has 2 dimensions(Month and Product). We are using Above() function in the line chart and it was displaying wrong numbers in the chart.

Above() function was working fine in the single dimension chart but not in dual dimension chart. Below is the example of expressions using in my chart.

Expression: Sum(Product TRx) / Sum(Total<Month> Market TRx)

Using Above: Sum(Product TRx) / Sum(Total<Month> Market TRx) - (Above(Sum(Product TRx) / Sum(Total<Month> Market TRx))

Please help me to understand how above() needs to be changed to work in this scenario.

Thanks in advance,

Jaswanth P

Re: Above function for two dimensions in line chart

swuehl — Mon, 16 May 2016 08:43:23 GMT

The above() will pick the above Product within the same Month (that's by the order of dimensions needed in your line chart), while you expect to pick the previous Month.

You can either work with an AsOf Table

Calculating rolling n-period totals, averages or other aggregations

or use the aggr() function to reorder your table dimensions in the chart expression:

=Aggr(

Sum(Product TRx) / Sum(Total<Month> Market TRx) - (Above(Sum(Product TRx) / Sum(Total<Month> Market TRx))

, Product, Month)

This will only work if you are using Month field values with chronologic load order or on newer QV versions using structured parameter:

The sortable Aggr function is finally here!

Re: Above function for two dimensions in line chart

sunny_talwar — Mon, 16 May 2016 10:02:54 GMT

If I may, I would like to suggest an alternative method of doing this. Instead of doing this using Above(), consider creating an As-Of table in the script which will make your life so much easier.

The As-Of Table‌

If you can provide a sample, we might be able to show you how the As-Of Table can work for your scenario.

Best,

Sunny

Re: Above function for two dimensions in line chart

Mon, 16 May 2016 14:40:08 GMT

Hi,

I have a similar requirement.

I need to show some of the metrics at month level and some at quarter level. My data consists of both Month start date and Quarter start date. I am able to achieve the functionality for monthly data using AsOf Table. I need to show some data for single dimension and some for multiple dimension, similar to Jaswanth's.

Can the same AsOf Table be used for showing both monthly as well as quarterly, or do we need to separate tables. Itried to modify the existing code a bit for quarterly view but couldn't achieve the desired functionality?

I am attaching my sample data (single metric only) and QVW file.