set analysis cumulated sum for several months

[omyahamburg]

Dear all

I hope that someone can help me.

I have a table with values over a calendar year.

I want to build up a set analysis, where I can define a date, e.g. 31.07.2012

The analysis should give me the following results:

Cumulated sum of values 01/2012

Cumulated sum of values 01/2012+02/2012

Cumulated sum of values 01/2012+02/2012+03/2012

Cumulated sum of values 01/2012+02/2012+03/2012+04/2012

Cumulated sum of values 01/2012+02/2012+03/2012+04/2012+05/2012

Cumulated sum of values 01/2012+02/2012+03/2012+04/2012+05/2012+06/2012

Cumulated sum of values 01/2012+02/2012+03/2012+04/2012+05/2012+06/2012+07/2012

Tanks a lot for your help.

Best regards

Joerg

[#2]

hi

in the expression tab -accumulation

select full accumulation -then see

[#3]

the above suggestion will work only in bar chart

u need something like this depending on the date format & exactly how many months u need or how many months u have in ur data:

for last 12 months :first create a field YearMoth from Year & Month fields

sum({$<Month=,Year=,

YearMonth={">=$(=Date(addmonths(Max(YearMonth), -11),'MMM YY')) <=$(=Date(addmonths(Max(YearMonth), 0),'MMM YY'))"}>}Sales)

[omyahamburg]

Thanks khushi143l, this helps at a first step 🙂

[omyahamburg]

I need an input field where I can define the end date, eg. 31.07.2012

The set analysis should give me for each month back to January

the cumulated sums.

I tried your formula, but it fails.

I do attach my example file, maybe you can show me how to ?

[#6]

Hi

Same in this accumulate option is there

it give upto particular steps

just check it  once

[Anonymous]

Hi Joerg,

Check the attached file and let me know if this is what you are looking for

[#8 · Accepted solution]

Please see the attached QV application.

Hope this helps

Regards

Mhatim

[CELAMBARASAN]

Below is the expression based on the Selected date in the date field

sum({<Date={">=$(=YearStart(Max(Date)))<=$(=MonthEnd(Max(Date)))"}>}Value)

Instead of Max(Date) part you can use the Variable with the Date value.

[omyahamburg]

Dear Mhatim

Thanks. That works pretty good.

Best regards

Joerg