Set Analisys Year And Month Acummulation

Tue, 12 Mar 2013 03:20:33 GMT

HI Dear QV Community

I have a problem while attempting a dynamic sales accumulated from months to years, this should work as follows

1) one year and month selcciona eg, 2012 and March

2) It indicates how many years you want to earn and how many months

The result may be as follows depending on the number of years and months indicated the desired accumulation

Example 1: Years 1 and months accumulation accumulation 0

Outcome 1: March 2012 sales more sales March 2011

Example 2: Years 1 and months accumulation Accumulation 1

Result 2: February sales over March 2012 over February sales over March 2011

Example 3: 2 years of accumulation and buildup months 0

Result 2: March 2012 sales more sales over March 2011 sales for March 2011

Example 4: Years 2 and month accumulation Accumulation 1

Result 2: Sales in February and March 2012 sales over February and March 2011 over sales in February and March 2011

Example 5: Years of accumulation accumulation 2 and 2 months

Result 2: Sales of January, February and March 2012 sales over January, February and March 2011 sales over January, February and March 2011

I tried with many Formulas, the last version is (Over Text Object)

=Money(Sum({$<Type={'EXP'}, Year={">=$(=Max(Year)-AcumYears)"},Month={'<=$(=Month(AddMonths(Max(Date),AcumMonths(*-1))))'}>} Sales))

AcumYears --> Variable with integer value to years for acummulation

AcumMonths --> Variable with integer value to months for acummulation

Month Field --> Date in ('MMM') format made from Script with Month(Date) as Month

Year--> Date in ('YYYY') format made from Script with Year(Date) as Year

The expresion works fine just with Type and Year set analysis delimiters, but with month parameter not work

Re: Set Analisys Year And Month Acummulation

nirav_bhimani — Tue, 12 Mar 2013 05:18:12 GMT

Hi,

Try this Expression for Example 3:

=Sum({<Date= P({<Year={$(=max(Year)-1)}>}Date),Type= {'EXP'}>}Sales)

Regards,

Nirav Bhimani

Tue, 12 Mar 2013 17:43:13 GMT

I Solved the issue with

=Money(Sum({$<Type={'EXP'}, Year={">=$(=Max(Year)-AcumYears)"},Month={">= $(=Max(Month)-AcumMonths)"}>} Sales))

Thanks