Cumulative Value based on date range?

[#1]

Hello,

I'm trying to display a table of cumulative values based on dates, and so far, I can only do this if a select the "Cumulative" Button in the Expression tab in my chart.  This works well if you display all dates/rows, but I need the cumulative value even if I select just one date/row.

I have attached a sample file that has the following script:

testtable:

LOAD * INLINE

[

Date, Widgets,

1/1/2013, 35

2/1/2013, 25

3/1/2013, 50

4/1/2013, 10

5/1/2013, 60

6/1/2013, 20

]

So I can display this as a table that show this data of Widgets per Month, or I can show it as a cumulative widget sum also using that button in the Expression.  But what I need is to be able to isolate a Date, like 4/1/2013, and show the *cumulative* sum of Widgets from the beginning of time through that date.  So if I selected just 4/1/2013, it should show (35+25+50+10) = 120.

Any thoughts?

[#2]

Hello,

Well, we are getting closer.  Your expression works but I cannot remove the date dimension for my needs.  I need to be able to keep the date dimension but still have it display the cumulative amount for that date, even if the previous dates are not selected in the table.    Any thoughts?

[#3]

You need to use Set Analysis as Julian showed you.

i had written another doc on that topic: http://community.qlik.com/docs/DOC-4951

But Set Analysis are computed ONCE per chart. You cannot use them if you expect a different "result" by row or column.

Fabrice

[#4]

I'm asking if there is any way to keep the a date dimension in place, but still have it display the cumulative amount for that date, even if the previous dates are not selected in the table.    Are you saying that is not possible?  If so, I'll abandon this attempt : )

[#5]

Chris,

That is possible by

1) creating an expression with a set analysis (Julian has given the way to write it)

2) having ONE date selected only (if you select several dates, the Max() function will return the last date anyway)

Fabrice

[#6]

Understood, thanks.  Unfortunately, our requirements dictate that I need to following:

1) A Date/Month (dimension) column that shows a rolling 12 or 24 months at a time

2) Expression showing the cumulative amount even if the start date/months are not being shown in the graph

[#7]

Then modify your model

Fabrice

[#8]

I'm sorry, but I'm not sure what to do next and I need more help... perhaps an example of an expression that will fit my needs or a sample file?