Sum of the sales until 'Date' given as Dimension.

[#1]

Dear all,

I am facing a problem that sounds easy to solve. Here is the problem :

The Data are :

Date          Sales

01/09/2012    1 Euros

03/09/2012    10 E

05/09/2012    100 E

06/09/2012    1000 E

10/09/2012    10000 E

I want this table :

Dimension : Date

Expr : Sum of the sales until the given date.

i.e. :

01/09/2012  1

02/09/2012  1

03/09/2012  11

04/09/2012  11

05/09/2012  111

06/09/2012  1111

07/09/2012  1111

08/09/2012  1111

09/09/2012  1111

10/09/2012  11111

I've tried very hard with set analysis. But nothing works. I'm probably missing something. Example : sum({$<Date={"<=$(=Date)"}>} Sales)

Thanks for your help in this exercise

Adrien

[swuehl · Accepted solution]

I don't think set analysis is the way to go here, because it's not evaluated in the context of your dimension.

Try using full accumulation option from expression properties or using rangesum(above(sum(Sales),0,rowno() ))

You may need to create a calendar table to get all Date values, not only the ones with data.

Hope this helps,

Stefan

[#3]

interesting answer. You've got the point. Thanks ! As in the previous time I've posted a question in the forum.

Concerning the calendar table, this post is really good and helped me to create one : http://community.qlik.com/qlikviews/1075

I will keep you informed if I succeed.

[#4]

Well, your answer fits my need !

I have created a Calendar table, I've plotted a chart with, as dimension :

=year(CalendarDate) & ' ' & month(CalendarDate)

and as expression :

rangesum(above(sum(value),0,$(vSTEP) ))/$(vSTEP)

And then I created a step by step slider to select the vSTEP variable. And it does work !!

Here is a file with the result.

Many thanks !