Accumulation through time

[mcornips]

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

[Mark_Little]

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

[sunny_talwar]

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

[mcornips]

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

[Mark_Little]

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

[mcornips]

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

[mcornips]

Here my qvw I referred to.