topic Re: RangSum per Year in App Development

RangSum per Year

martinr_ — Fri, 21 Apr 2017 10:25:56 GMT

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

sunny_talwar — Fri, 21 Apr 2017 11:03:32 GMT

Try this

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

Re: RangSum per Year

martinr_ — Fri, 21 Apr 2017 11:17:30 GMT

Hi Sunny !

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

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 11:21:36 GMT

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

Re: RangSum per Year

martinr_ — Fri, 21 Apr 2017 11:25:39 GMT

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

sunny_talwar — Fri, 21 Apr 2017 11:29:33 GMT

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

Re: RangSum per Year

martinr_ — Fri, 21 Apr 2017 11:46:51 GMT

2015/2015 or 2016/2017 is fiscalyear.

the other one is the week from an datefield.

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 11:48:45 GMT

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

Re: RangSum per Year

martinr_ — Fri, 21 Apr 2017 12:03:57 GMT

No i have used other.

How can i shared it with data?

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 12:05:34 GMT

Look here on how to upload: Uploading a Sample

Re: RangSum per Year

martinr_ — Fri, 21 Apr 2017 13:30:21 GMT

Here is the Testfile !

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 13:35:11 GMT

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

martinr_ — Fri, 21 Apr 2017 13:47:21 GMT

Sorry, i am new with this.

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

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 13:57:29 GMT

While loading your data

LIB CONNECT TO 'MCQLIK (mc_qservice)';

LOAD *,

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

martinr_ — Fri, 21 Apr 2017 14:09:44 GMT

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

The sum of KW13 is displayed at KW01.

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 14:18:44 GMT

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

martinr_ — Fri, 21 Apr 2017 14:53:52 GMT

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)))

Re: RangSum per Year

sunny_talwar — Fri, 21 Apr 2017 15:02:25 GMT

So everything working good now? Please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny