Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

  

DateQty
20.12.2016 13:24:431,000
22.12.2016 14:40:108,000
22.12.2016 15:48:362,000
23.12.2016 12:45:401,000

Then i load second table:

 

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:

  

Date2Result
21.12.20161
22.12.20161
23.12.201611
24.12.201612
25.12.201612
26.12.201612
27.12.201612
28.12.201612
29.12.201612
30.12.201612
31.12.201612

Must be:

  

Date2Result
20.12.20161
21.12.20161
22.12.201611
23.12.201612
24.12.201612
25.12.201612
26.12.201612
27.12.201612
28.12.201612
29.12.201612
30.12.201612
31.12.201612

Can anyone thinks about my mistake in expr?

Thanks.

9 Replies
swuehl
MVP
MVP

Your Date values are in fact timestamps:

Date sum(Qty)Num(Date)
20.12.2016142724,558831019
22.12.2016842726,611226852
22.12.2016242726,65875
23.12.2016142727,531712963

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

sunny_talwar

Try this:

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

Capture.PNG

sunny_talwar

Or you can try with floor

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

swuehl
MVP
MVP

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

LOAD DayName(Date) as Date,

     Qty

FROM

Test.xlsx

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

swuehl
MVP
MVP

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

sunny_talwar

Agreed

Anonymous
Not applicable
Author

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

date(left(Date,11)) as Date

swuehl
MVP
MVP

Андрей Шепель 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?

sunny_talwar

Although this might work, I would suggest to use this

Date(Floor(Date)) as Date

or as Stefan said

DayName(Date) as Date