topic Re: Accumulation through time in QlikView

Accumulation through time

mcornips — Wed, 25 Nov 2015 12:53:24 GMT

Hallo,

Does anybody have an idea how I can reach accumulation by the dimension Time/Periods in a formula that still works if I select specific time intervals from the master calendar fields.

For example, if I select Year '2014' Qlikview will not show me the values for 2014, but the values until 2014 (including the values of the preceeding years).

I want to compare the accumulated amount of particular investments with the revenue on this investment of a particular period.

So when I select the period from the master calendar fields, I want to see the accumulated amount of the investment until that period and the revenue of that period.

Regards,

Maurice

Re: Accumulation through time

Mark_Little — Wed, 25 Nov 2015 13:01:38 GMT

HI,

The way i would approach this is first get the max value selected into a variable

vSelectdate=MAX(GetFieldSelections(Date))

Then you can Compare this in your set analysis

SUM({<Date={'<=$(vSelectdate)'}>}Value)

If need you can exculde other selections

SUM({<Month=,Date={'<=$(vSelectdate)'}>}Value)

Mark

Re: Accumulation through time

sunny_talwar — Wed, 25 Nov 2015 13:05:09 GMT

Like this?

Script:

Table:

LOAD *,

Month(Date) as Month,

Year(Date) as Year,

MonthName(Date) as MonthYear,

Ceil(Rand() * 100000) as Value;

LOAD Date(MakeDate(2009, 12, 31) + RecNo()) as Date

AutoGenerate (MakeDate(2015,12,31) - MakeDate(2009, 12, 31));

Straight Table

Dimension: MonthYear

Expressions

1) =Sum(Value)

2) =If(Sum(Value) > 0, RangeSum(Above(Sum({<Month, Year, MonthYear, Date>}Value), 0, RowNo())))

Re: Accumulation through time

mcornips — Wed, 25 Nov 2015 13:22:37 GMT

Hi Mark,

Using your formula and then selecting a year from the master calendar fields still only shows me the values of that selected year and not the values until that year.

Maurice

Re: Accumulation through time

Mark_Little — Wed, 25 Nov 2015 13:58:30 GMT

Hi,

Try

SUM({1<Date={'<=$(vSelectdate)'}>}Value)

If you want certain selections to effect you values add like below

=SUM( {1<Field_1=$::Field_1, Date={'<=$(vSelectdate)'}>} Value)

Mark

Re: Accumulation through time

mcornips — Thu, 10 Dec 2015 10:25:57 GMT

Hi Mark,

I tried your formula in the QVW as attached, in the (red) bar chart in the second tab. I would like to compare the values of the period with the accumulated values until that period. So if I select a year, quarter or month in the master calendar fields the particular expression always will show me the accumulated value until that date. So if I select for example:

- 2010

- January

- Dimension 0

the expression shows me the value 27,276

Regards,

Maurice

Re: Accumulation through time

mcornips — Thu, 10 Dec 2015 13:00:39 GMT

Here my qvw I referred to.