Re: Wrong accumulative sum by independent field - Qlik Community

Anonymous · ‎2017-01-24

Colleagues, good day!

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

I have one simple table with such fields:

Then i load second table:

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:

Must be:

Can anyone thinks about my mistake in expr?

Thanks.

swuehl · ‎2017-01-24

Your Date values are in fact timestamps:

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

sunny_talwar · ‎2017-01-24

Try this:

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

sunny_talwar · ‎2017-01-24

Or you can try with floor

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

swuehl · ‎2017-01-24

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 · ‎2017-01-24

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

sunny_talwar · ‎2017-01-24

Agreed

Anonymous · ‎2017-01-24

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

date(left(Date,11)) as Date

swuehl · ‎2017-01-24

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

sunny_talwar · ‎2017-01-24

Although this might work, I would suggest to use this

Date(Floor(Date)) as Date

or as Stefan said

DayName(Date) as Date