5 Replies Latest reply: Jul 26, 2016 5:14 PM by Azmina Virani

# Above() function with multiple dimensions

Hi,

Please find the attached QV with my problem.

Here is a description:

I use the above function to calculate projected stock values. Now I want to display the projected stock values on date and product group (two dimensions). So I introduced TOTAL in the expression to take care of the "multiple dimensions with above function"-problem, so far so good. The pivot returns the correct, wanted figures, however when I try to convert it to a line chart (with the various product group dsiplayed as seperate lines) I do not get it to work.

Note that flipping the line chart to a pivot chart returns the correct figures in the pivot. Also, chosing one 'Product Group' works fine and returns the correct results for both line chart and pivot.

How do I get my line chart to work??

BR,

David

• ###### Re: Above() function with multiple dimensions

Try using the option Accumulate 99 Steps Back instead of the rangesum(above... construction for the line chart. See attached example

• ###### Re: Above() function with multiple dimensions

Thank you very much Gysbert! Works perfect!

BR

David

• ###### Re: Above() function with multiple dimensions

Your issue is that your dimensions are Date as first dimension and Product Group as second dimension, while you probably want to aggregate your values by Date per Product Group (i.e. Product Group as first dimension, Date as second). It only works in your pivot table because you pivoted Product Group to the top.

When comparing a table to a line chart, you need to look at a straight table chart, which shows the same problem, right?

To get around this issue (showing your data in a different dimension order than when calculating the rangesum()), you can use advanced aggregation:

=

aggr(

RangeSum(Above( Sum(ReplenishmentVolume),0,rowno()))

-

RangeSum(Above(Sum( {<SalesDatum={">\$(=(Date(Today()-92,'YYYY-MM-DD'))) "}>} HistoricSalesVolym) / 90,0,rowno()))

+

RangeSum(Above( Sum(StockVolume),0,rowno()))

,P_Varugruppnr, P_Date)

Note the different dimension order. I assume you want to reset the rangesum for each product group, so you need to remove the total qualifier again. I also replaced the 99 with rowno(), but not sure if 99 is a special requirement for you (running total for last 99 values?).

Last, to make this work, you need Date dimension values load order to be sorted chronological (because aggr() function sorts its dimension values only by load order).

This is not the case yet in your document!

After you've done that, your chart should work as expected.

• ###### Re: Above() function with multiple dimensions

Thank you Swuehl for the input! Really helpful!

BR

David

• ###### Re: Above() function with multiple dimensions

I am unable to open your QVW, I am having a similar issue in QlikSense, could you explain what you did here?

here is a link to my post for clarification-

Cumalitve Chart For Multiple Years

Thanks!