Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Set Analisys Year And Month Acummulation

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

1 Solution

Accepted Solutions
Not applicable

Re: Set Analisys Year And Month Acummulation

I Solved the issue with

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

Thanks

2 Replies
nirav_bhimani
Contributor III

Re: Set Analisys Year And Month Acummulation

Hi,

Try this Expression for Example 3:

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

Regards,

Nirav Bhimani

Not applicable

Re: Set Analisys Year And Month Acummulation

I Solved the issue with

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

Thanks

Community Browser