Above function for two dimensions in line chart

[polisetti]

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

[swuehl]

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

[#4]

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.