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]

Chris,

See a document I have published to create simple YTD:

http://community.qlik.com/docs/DOC-4821

You may also use set analysis to do the computation, but this one will be valid only for ONE period (the set is unique for all the chart): http://community.qlik.com/docs/DOC-4951

Fabrice

[aveeeeeee7en]

Hi Chris

Use this Expression in your Script:

RangeSum(Above(total sum(Widgets),0,RowNo()))

Also see the Attachment.

[maxgro]

an good doc about..........

Calculating rolling n-period totals, averages or other aggregations

[#5]

Excellent doc (not just good )

To me, an AsofTable is needed in ths case and both documents explain how to do.

Fabrice

[#6]

Sorry this only works if you retain all rows in the chart..  I need an expression that will work if I select *only one date/row* and still retain the cumulative 'YTD' amount.

[#7]

Does the AsofTable method work only for a fixed rolling period?  I need the expression to be able to adjust to *any* period of months, etc.

[#8]

1) YES . You need to adapt your model to your needs. And populate this table to the computations you will do

2) If you want to get only one period in your chart (and do a computation), you can use also set analysis

Fabrice

[#9]

I'm sorry, but can you provide an example of an expression that would actually work if it were in fact the only row I selected?  In other words, show the cumulative amount even if I only select one date/month, etc.?

[#10]

Hi Chris,

sum ({<Date={"<=$(=date(max(Date),))"}>}Widgets)

As Aunez suggested try set analysis. Above is your expression and remove Date as your Dimension to get your accumulation in one figure.  You could put this into a variable to make it simpler.

Julian

See attachment