Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi
in the expression tab -accumulation
select full accumulation -then see
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)
Thanks khushi143l, this helps at a first step 🙂
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 ?
Hi
Same in this accumulate option is there
it give upto particular steps
just check it once
Hi Joerg,
Check the attached file and let me know if this is what you are looking for
Please see the attached QV application.
Hope this helps
Regards
Mhatim
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.
Dear Mhatim
Thanks. That works pretty good.
Best regards
Joerg