topic Re: cumulative sum style question in QlikView

cumulative sum style question

Fri, 14 Sep 2012 12:45:31 GMT

Hi Guys,

Can anyone help me have a column with cumulative sales info from the beginning of time in my data i.e. Jan 2008?

Thanks

Re: cumulative sum style question

swuehl — Fri, 14 Sep 2012 12:49:02 GMT

Try an expression like

=rangesum(above(sum(Sales),0,RowNo()))

Re: cumulative sum style question

Fri, 14 Sep 2012 12:52:20 GMT

Thanks swuehl, that is exactly where I am currently. However I need to go one step further.

What do I add to the expression so that I can select 2009 and April from the dimension and it then I still get a cumulative from Jan-2008 through to April 2009?

Re: cumulative sum style question

swuehl — Fri, 14 Sep 2012 13:25:56 GMT

Maybe

=rangesum(above(sum({<[Transaction Date] = {"<=$(=max([Transaction Date]))"}, Year, Month>}Sales),0,RowNo()))

Re: cumulative sum style question

Fri, 14 Sep 2012 14:19:01 GMT

That works! thanks.

Now my issue is if I replace the date dimensions with the Products it doesn't work. I want a tbale that gives me sales to date (right from the beginning of time) for each product. I initially simplified it think that the next bit would work fine.

Re: cumulative sum style question

swuehl — Fri, 14 Sep 2012 15:54:24 GMT

Then your SalesToDate expression might be a good start. What about changing it to

=sum( {$<[Transaction Date]={"<=$(vEnd)"},Year,Month >} Sales)

Re: cumulative sum style question

Mon, 24 Sep 2012 08:25:34 GMT

swuehl, thanks for the help. I used a mixture of your suggestion and a bit of research into other things and came up with what I needed. It looks something like this -

=sum({<[Transaction Date] = {"<=$(=max([Transaction Date]))"}, Year, Month>}Sales)

Seems to work so far.

Thanks again