9 Replies Latest reply: Jan 24, 2017 2:48 PM by Sunny Talwar

Wrong accumulative sum by independent field

Colleagues, good day!

Some questions about calculation in expression, which use logic of accumulative sum.

I have one simple table with such fields:

 Date Qty 20.12.2016 13:24:43 1,000 22.12.2016 14:40:10 8,000 22.12.2016 15:48:36 2,000 23.12.2016 12:45:40 1,000

 Date2 01.12.2016 02.12.2016 03.12.2016 04.12.2016 05.12.2016 06.12.2016 07.12.2016 08.12.2016 09.12.2016 10.12.2016 11.12.2016 12.12.2016 13.12.2016 14.12.2016 15.12.2016 16.12.2016 17.12.2016 18.12.2016 19.12.2016 20.12.2016 21.12.2016 22.12.2016 23.12.2016 24.12.2016 25.12.2016 26.12.2016 27.12.2016 28.12.2016 29.12.2016 30.12.2016 31.12.2016

and calculate in pivot sum of Qty by Date2 field with such logic: each value for next date must add previous sum of Qty:

my expr:

sum(if(Date2>=Date, Qty))

in result i get wrong values:

 Date2 Result 21.12.2016 1 22.12.2016 1 23.12.2016 11 24.12.2016 12 25.12.2016 12 26.12.2016 12 27.12.2016 12 28.12.2016 12 29.12.2016 12 30.12.2016 12 31.12.2016 12

Must be:

 Date2 Result 20.12.2016 1 21.12.2016 1 22.12.2016 11 23.12.2016 12 24.12.2016 12 25.12.2016 12 26.12.2016 12 27.12.2016 12 28.12.2016 12 29.12.2016 12 30.12.2016 12 31.12.2016 12

Can anyone thinks about my mistake in expr?

Thanks.

• Re: Wrong accumulative sum by independent field

Your Date values are in fact timestamps:

 Date sum(Qty) Num(Date) 20.12.2016 1 42724,558831019 22.12.2016 8 42726,611226852 22.12.2016 2 42726,65875 23.12.2016 1 42727,531712963

edit: and your Date2 values are integers, i.e. refer to the start of day

• Re: Wrong accumulative sum by independent field

Try this:

Sum(If(DayName(Date2) >= DayName(Date), Qty))

• Re: Wrong accumulative sum by independent field

Or you can try with floor

Sum(If(Floor(Date2) >= Floor(Date), Qty))

• Re: Wrong accumulative sum by independent field

Or fix it in the script (I think this is what you tried to do anyway):

Qty

FROM

Test.xlsx

(ooxml, embedded labels, table is Лист1);

• Re: Wrong accumulative sum by independent field

And it might be better to use another approach instead of the data island (which is not really performing well on large data):

The As-Of Table

• Re: Wrong accumulative sum by independent field

Agreed

• Re: Wrong accumulative sum by independent field

Thanks) Or use only DD.MM.YYY part:

date(left(Date,11)) as Date

• Re: Wrong accumulative sum by independent field

Андрей Шепель wrote:

Thanks) Or use only DD.MM.YYY part:

date(left(Date,11)) as Date

I wouldn't mess with string functions here.

Use existing Date / Time functions and interpret your values when reading in.

Get the Dates Right

Why don’t my dates work?

• Re: Wrong accumulative sum by independent field

Although this might work, I would suggest to use this

Date(Floor(Date)) as Date

or as Stefan said

DayName(Date) as Date