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: 
Not applicable

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

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!

You need to create your date field values in a chronological order first, before loading in your facts.

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

Not applicable
Author

Thank you very much Gysbert! Works perfect!

BR

David

Not applicable
Author

Thank you Swuehl for the input! Really helpful!

BR

David

Not applicable
Author

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!