17 Replies Latest reply: Apr 21, 2017 11:02 AM by Sunny Talwar

# RangSum per Year

I have the following dimensions year-calendar week.

Now i want to have a cumulative sum per year.

But should always start at the beginning of the year.

This formula does not start at zero at the beginning of the year in an line diagram. in an tabel it is fine.

I do not find a solution.

RangeSum(Above(Sum({<TYP={'SALES'}>}PRICE),0,RowNo()))

• ###### Re: RangSum per Year

Try this

Aggr(RangeSum(Above(Sum({<TYP={'SALES'}>}PRICE),0,RowNo())), FiscalYear, (Week, (Numeric)))

• ###### Re: RangSum per Year

Hi Sunny !

If I use the so, the line runs to 0.

• ###### Re: RangSum per Year

What are the two dimensions that you use in your chart on the right?

• ###### Re: RangSum per Year

KW

Dual(Num(YEAR([DATUM]),00)&'-KW'&Num(Week([DATUM]),00),Num(YEAR([DATUM]),00)&NUM(Week([DATUM]),00))

Nettopreis

Aggr(RangeSum(Above(Sum(.........

• ###### Re: RangSum per Year

Not talking about the line chart.... but the chart next to it... What are the two dimensions used here?

• ###### Re: RangSum per Year

2015/2015 or 2016/2017 is fiscalyear.

the other one is the week from an datefield.

• ###### Re: RangSum per Year

Have you used these two dimensions in your chart? Would you be able to share a sample?

• ###### Re: RangSum per Year

No i have used other.

How can i shared it with data?

• ###### Re: RangSum per Year

Here is the Testfile !

• ###### Re: RangSum per Year

Create Week(DATUM) as Week in the script and then try this

Aggr(RangeSum(Above(Sum({<BELEGTYP={'AB'}>}GESPREISNETTO),0,RowNo())), GESCHAEFTSJAHR, (Week, (NUMERIC)))

• ###### Re: RangSum per Year

Sorry, i am new with this.

Do you mean with script when loading data, or a new dimension?

• ###### Re: RangSum per Year

LIB CONNECT TO 'MCQLIK (mc_qservice)';

Week(DATUM) as Week;

SQL SELECT * FROM VERKAUF order by geschaeftsjahr,datum;

SET DateFormat='YYYY.MM.DD';

SET TimestampFormat='YYYY.MM.DD hh:mm:ss[.fff]';

• ###### Re: RangSum per Year

We are approaching.
The arrangement does not fit so completely yet.

The sum of KW13 is displayed at KW01.

• ###### Re: RangSum per Year

To me it looks like the 8035624 is associated with 2016-KW52.... May be create a table object with the same dimension and measure and see which YearWeek correspond to which value...

• ###### Re: RangSum per Year

our Businessyear ist not from 01 - 52. That i think is the problem.

when i script it so, all is fine.

Aggr(RangeSum(Above(Sum({<BELEGTYP={'AB'}>}GESPREISNETTO),0,RowNo())), GESCHAEFTSJAHR, (Week, (NUMERIC)))