topic Cumulative Value based on date range? in QlikView

Cumulative Value based on date range?

Sun, 23 Feb 2014 17:57:45 GMT

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?

Re: Cumulative Value based on date range?

Sun, 23 Feb 2014 18:02:48 GMT

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

Re: Cumulative Value based on date range?

aveeeeeee7en — Sun, 23 Feb 2014 18:59:40 GMT

Hi Chris

Use this Expression in your Script:

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

Also see the Attachment.

Re: Cumulative Value based on date range?

maxgro — Sun, 23 Feb 2014 20:23:11 GMT

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

Calculating rolling n-period totals, averages or other aggregations

Re: Cumulative Value based on date range?

Sun, 23 Feb 2014 20:39:36 GMT

Excellent doc (not just good )

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

Fabrice

Re: Cumulative Value based on date range?

Mon, 24 Feb 2014 02:37:23 GMT

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.

Re: Cumulative Value based on date range?

Mon, 24 Feb 2014 03:11:44 GMT

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.

Re: Cumulative Value based on date range?

Mon, 24 Feb 2014 08:11:12 GMT

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

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 02:10:52 GMT

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.?

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 04:15:51 GMT

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

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 14:00:48 GMT

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?

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 14:13:21 GMT

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

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 14:45:35 GMT

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 : )

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 14:57:44 GMT

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

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 15:06:47 GMT

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

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 15:09:28 GMT

Then modify your model

Fabrice

Re: Cumulative Value based on date range?

Tue, 25 Feb 2014 15:36:19 GMT

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?