topic Re: Sum of the sales until 'Date' given as Dimension. in QlikView

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

Wed, 19 Sep 2012 15:14:23 GMT

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

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

swuehl — Wed, 19 Sep 2012 15:25:31 GMT

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

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

Wed, 19 Sep 2012 16:05:37 GMT

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.

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

Wed, 19 Sep 2012 16:48:06 GMT

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 !