Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Taking into account dimension in line chart expression

Hi all,

After my last attempt to find a solution to tricky situation, I'm turning to you once again.

I have a line chart which for every value in the X axis I need my calculation to calculate its value according up until the specific value in the X axis.

For example, let's say that I want to calculate the StDev of samples I took per quarter. Suppose I have data for Q'1, Q'2, Q'3.

I'd like to see the following:

For Q'4 the StDev of Q'4-Q'1 (even though Q4 doesn't have any values, we'll actually see StDev of Q'3-Q'1),

In Q'3 the Stdev of Q'3-Q'1,

In Q'2 the Stdev of Q'2-Q'1

and for Q'1 the StDev of Q'1.

Have any idea how to work it out?


Thanks in advance!!!

3 Replies
swuehl
MVP
MVP

Have you tried a combination of chart range functions with chart inter record functions, like rangestdev() and above()?

Something along these  lines:

=rangestdev(above( Sample),0, rowno() )

Not applicable
Author

I tried it... unfortunately it didn't work. But maybe I implemented it wrongly.

I actually make the StDev (along with few others calculation) in a variable.

What that I'm trying to calculate as I written above, looks like this:

Count(if(IsNum($(Pp_Calc)),System_Key))

Trying to implement your suggestion looks like this:

rangesum(Above(count(distinct if(IsNum($(Pp_Calc)),System_Key))),0,5)

But it doesn't do the trick.

Also, I want for the first column to take into account periods which aren't available in the chart due to user selections.

Thanks!

swuehl
MVP
MVP

'It doesn't do the trick' is not really helpful here.

You need to describe your setting, what you expect to see and what you actually see as detailed as possible.

Most of the time, it is easiest if you can upload a small sample application that demonstrates your issue.

In general, what you are trying to achieve might be possible with several approaches. It looks like you are trying to calculate a rolling average.

There are some samples here in the forum how to do that, Gysbert has posted a nice document that discusses some alternatives incl. advanced aggregation and an as-of-table.

Calculating rolling n-period totals, averages or other aggregations