# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## Calculation YTD by month selected

Hi Guys,

How to write script, if I want to calculate YTD by month selected.

for example:

if I selected year 2014 and month may then accumulation (Jun13 - May14)

or if I selected year 2014 and month apr then accumulation (May13 - Apr 14)

or if I selected year 2013 and month jun then accumulation (Jul12 - Jun13)

thanks & regards..

1 Solution

Accepted Solutions
Not applicable

## Re: Calculation YTD by month selected

Hi Jagan,

I was try your script, but the numbers accumulate all previous year 2013-2009.

and

I have a script :

I think the script is true, because when I checked this script accumulate 12 months of Previous Year.

Btw, Thanks Sir Jagan...

Regards..

24 Replies
Valued Contributor III

## Re: Calculation YTD by month selected

Try somthig like in Expressions:

Sum({<Date={">=\$(=AddMonths(Max(Date),-11)) <=\$(=Max(Date))"} >} [Measure Field] )

Not applicable

## Re: Calculation YTD by month selected

Hi Valdi

You can try this

=SUM({<Year={'\$(vMaxYear)'},Date={'<=\$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

Regards

Rishi Beri

Valued Contributor III

## Re: Calculation YTD by month selected

Hi Rishi.

If you set Year as Max(Year) how do you get Previous Year data. This expression calculate data of a perticular year to till date.

Ex if you select year 2014 and month Jun then you would get data of 01-Jan-14 to 30-Jun-14. but the Valdi's requirement is different what you have thought.

Not applicable

## Re: Calculation YTD by month selected

For previous year calculation you can use

=SUM({<Year={'\$(vPriorYear)'},Month=,Day=,Date={'<=\$(vPriorYearDate)'},MonthYear=>} MeasureField)

vMaxYear = max(Year)

vPriorYear = vMaxYear -1

vPriorYearDate = date(addyears(max(Date), -1), 'DD MMM YYYY')

Regards

Rishi Beri

Not applicable

## Re: Calculation YTD by month selected

I'v tried your script, but only accumulate 1 month not 12 months,

sum({Date= <--- Date or Months?

Thanks & Regards.

Valued Contributor III

## Re: Calculation YTD by month selected

I thing you have just misunderstood the actual requirement. Valdi  wants if anyone selects year 2014 and month May then accumulation should be from Jun13 to May14.

Did you get the desired result by using following expressions what you have suggested?

=SUM({<Year={'\$(vMaxYear)'},Date={'<=\$(vMaxDate)'},Month=,Day=,MonthYear=>} MeasureField)

vMaxYear = max(Year)

vMaxDate = max(Date)

MVP & Luminary

## Re: Calculation YTD by month selected

Hi,

Try this expression

=Sum({<Year=, Month=, Date={">=\$(=MonthStart(Max(Date),-12)) <=\$(=Max(Date))"} >} [Measure Field] )

Replace Year=, Month=, Date= with your actual field names.

Regards,

Jagan.

Not applicable

## Re: Calculation YTD by month selected

Hi Jagan,,

I've tried your script, but the result is the difference..

Valued Contributor III

## Re: Calculation YTD by month selected

Can you share sample qvw?

Community Browser