topic Re: Accumulation by specific dimensions in QlikView

Accumulation by specific dimensions

Sun, 25 Jan 2015 12:46:58 GMT

Hello community,

Please can you check the attached app that describes my request.

I'm trying to accumulate the sales by year.

The report is by Year, Quarter.

But when I add a 3rd dimension, like month, the accumulation breaks at the change of quarter.

The final expression (that I'll apply in the real scenario) should work regardless of the number of dimensions in the report and the number of dimensions to accumulate by.

Appreciate your help.

Thanks.

Re: Accumulation by specific dimensions

Anonymous — Sun, 25 Jan 2015 14:54:47 GMT

Hi!

Pay attention because in the full table (Year, Quartre, Month) Rowno() goes from 1 to 3, so at the change of Quarter the above function restart the rangesum function and you lose the cumulation.

Re: Accumulation by specific dimensions

Sun, 25 Jan 2015 16:34:49 GMT

Hi Elena,

Thx for replying.

Indeed what you mentioned is already observed .

And what I'm looking for is a tweak/solution to be able to accumulate as needed.

Re: Accumulation by specific dimensions

cwolf — Sun, 25 Jan 2015 18:01:27 GMT

Hi,

to get a complete accumulation over all lines, you have to use:

sum(Sales) + RangeSum(Above(Total Sum(Sales),1,RowNo(TOTAL)-1))

or without incorrect subtotals:

if(Dimensionality()=0 or Dimensionality()=Max(Dimensionality()), sum(Sales) + RangeSum(Above(Total Sum(Sales),1,RowNo(TOTAL)-1)))

Regards

Christian

Re: Accumulation by specific dimensions

Mon, 26 Jan 2015 03:25:57 GMT

Hi Christian,

An overall accumulation is not exactly what I'm looking for.

I want to accumulate by year even if I have more than 2 dimensions in the pivot.

Thanks.

Re: Accumulation by specific dimensions

Mon, 26 Jan 2015 09:33:40 GMT

Any hint ?

Re: Accumulation by specific dimensions

agomes1971 — Mon, 26 Jan 2015 14:30:42 GMT

Hi,

see attached.

HTH

André Gomes

Re: Accumulation by specific dimensions

Mon, 26 Jan 2015 14:45:31 GMT

Hi Andre,

That's accumulating the sales overall.

I need to accumulate by Year.

Thx anyways

Re: Accumulation by specific dimensions

cwolf — Mon, 26 Jan 2015 21:32:35 GMT

Hi,

for this, you need an indicator if dimension Year changed - depends on the last dimension Month:

aggr(min(Month),Year) -> has only a value for the first month in year.

The new expression needs a name because of recursive calling - AccuByYear:

if(aggr(min(Month),Year)>0,SumSales,SumSales+Above(Total AccuByYear))

See attachment.

Regards

Christian

Re: Accumulation by specific dimensions

Wed, 11 Feb 2015 10:43:38 GMT

Hi Christian,

No doubt your solution moved us closer.

But when we try to apply same in our scenario (having strings and not only numbers), we're facing some issues.

Could you please check the attached app ?

What we need is to be able to sort the pivot table by these fields in order (noting that ar_duedate2 is numeric and ar_duedate is formatted as date dd/mm/yyyy):

customer asc,

type (invoiced then unapplied),

routing (routingcode asc),

due date asc,

doc no asc

then the accumulation to be done by customer and type (so to have the accumulation restart on change of customer or type).

Appreciate yours and other community members help.