Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
carolin01
Luminary Alumni
Luminary Alumni

Aggr & Rangesum

Hi,

If the following formula:

Rangesum(Above(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount$(vCurrency)), 0,rowno())))

The issue is that I use it in a Pivot table having three dimensions: FYear, FPYearDesc and Transaction Date. What I want is that the Rangesum calculates the sum only within a FPYearDesc (means a period). Or in other words an aggregation within one month like this:

Value of Day 1 = Day 1,

Value of Day 1+2 = Value Day 2

Values of Day 1+2+3 = Value Day 3

What it does now is it does a calculation over all values no matter which FPYearDesc. How do I need to adjust it? I tried it with Aggr, but then I don´t get the value in each line...only in the first line then.

Many thanks for your help!

1 Solution

Accepted Solutions
sunny_talwar

Try this:

=If(Dimensionality() = 3, Rangesum(Above(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount), 0, RowNo())), Sum(NettAmount))

View solution in original post

10 Replies
Digvijay_Singh

Try using Dimensionality() or total<Dim...> function or can you share small sample to try with.

sunny_talwar

Like digvijay‌ would you be able to share a sample with the expected output to provide you with better help?

swuehl
MVP
MVP

How does your pivot table chart layout look like? Do you have any dimension pivoted? And which dimension is last and second to last w.r.t sort order?

In a pivot table,you might need to use another chart inter record function (e.g. Before() instead of Above() ) for pivoted dimensions.

And the chart inter record functions will consider column / row segments, so Transaction Date need to be the last in sort order to reset the Rangesum accordingly at segment bounderies.

carolin01
Luminary Alumni
Luminary Alumni
Author

It is FYear / FPYearDesc / TransactionDate.

carolin01
Luminary Alumni
Luminary Alumni
Author

I added a sample qvw now. Please see above.

sunny_talwar

I am still not sure what your desired output is, but is this what you want?

Capture.PNG

Expression:

Rangesum(Above(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount), 0, RowNo()))

rubenmarin

Hi Carolin, probably I misunderstood something, I tried your expression (adapted) and it seems to work.

Only issue I found is when some parts of the pivot table are colapsed and others expanded, I added a condition trying to check this:

If(IsNull(rowno()), Sum(NettAmount), Rangesum(Above(sum(NettAmount), 0,rowno())))

In this sample seems to work but in a more complex table it can fail

carolin01
Luminary Alumni
Luminary Alumni
Author

Very close. The formula is already correct. Is there a possibility to show 50 wen e.g. June 2016 is collapsed? And 110 when 2016 is collapsed?

sunny_talwar

Try this:

=If(Dimensionality() = 3, Rangesum(Above(sum({$<OrderPhase_Desc = {'Revenue'}>}NettAmount), 0, RowNo())), Sum(NettAmount))