topic Re: Aggr & Rangesum in QlikView

Aggr & Rangesum

carolin01 — Mon, 13 Jun 2016 10:34:04 GMT

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!

Re: Aggr & Rangesum

Digvijay_Singh — Mon, 13 Jun 2016 10:43:01 GMT

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

Re: Aggr & Rangesum

sunny_talwar — Mon, 13 Jun 2016 10:47:02 GMT

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

Re: Aggr & Rangesum

swuehl — Mon, 13 Jun 2016 10:56:17 GMT

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.

Re: Aggr & Rangesum

carolin01 — Mon, 13 Jun 2016 12:30:31 GMT

It is FYear / FPYearDesc / TransactionDate.

Re: Aggr & Rangesum

carolin01 — Mon, 13 Jun 2016 12:44:34 GMT

I added a sample qvw now. Please see above.

Re: Aggr & Rangesum

sunny_talwar — Mon, 13 Jun 2016 12:49:02 GMT

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

Expression:

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

Re: Aggr & Rangesum

rubenmarin — Mon, 13 Jun 2016 12:54:13 GMT

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

Re: Aggr & Rangesum

carolin01 — Mon, 13 Jun 2016 13:00:01 GMT

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?

Re: Aggr & Rangesum

sunny_talwar — Mon, 13 Jun 2016 13:08:12 GMT

Try this:

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

Re: Aggr & Rangesum

carolin01 — Mon, 13 Jun 2016 15:35:23 GMT

Many thanks for all of your help. As my real case was a lot more complex the combination of everything helped a lot so that I could solve it now 🙂