Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
polisetti
Creator II
Creator II

Above function for two dimensions in line chart

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

3 Replies
swuehl
MVP
MVP

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!

sunny_talwar

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

Not applicable

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.