24 Replies Latest reply: Jun 17, 2014 6:40 AM by jagan mohan rao appala

# 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..

• ###### Re: Calculation YTD by month selected

Try somthig like in Expressions:

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

• ###### 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.

• ###### 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.

• ###### Re: Calculation YTD by month selected

Hi Jagan,,

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

• ###### Re: Calculation YTD by month selected

Can you share sample qvw?

• ###### Re: Calculation YTD by month selected

Well Done,

I've tried,

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

• ###### Re: Calculation YTD by month selected

Hi,

Try this expression

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

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

Regards,

Jagan.

• ###### Re: Calculation YTD by month selected

Hi Jagan,

if I use a script -12 then accumulated to 13 months, but if I use the script -11 then accumulated to 12 months (correct).

• ###### Re: Calculation YTD by month selected

Yes Valdi.

Regards,

Jagan.

• ###### Re: Calculation YTD by month selected

Can you share sample qvw?

• ###### 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

• ###### 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.

• ###### 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

• ###### 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)

• ###### Re: Calculation YTD by month selected

=SUM({<YearMonth={'>=\$(VminYear) <=\$(VmaxYear )'}>}  Measure)

VmaxYear = GetFieldSelections(Year) & GetFieldSelections(Month)

Regards,

Prabhu

• ###### Re: Calculation YTD by month selected

Hi Jagan,

I need your help, how to write script if I want to see Prior Year with script YTD:

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

• ###### Re: Calculation YTD by month selected

Hi,

This will give you the sum of measure for the last 12 months.

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

For example, your max date is jun-2014, then the above exp will you sum of Measure for Jul-2013 to Jun-2014.

Regards,

jagan.

• ###### Re: Calculation YTD by month selected

Hi Jagan,

• ###### Re: Calculation YTD by month selected

Hi,

If you want from Jan-13 - Dec-13, then use this

=sum({<Year=, Month=, Date={">=\$(=YearStart(Max(Date),-1))<=\$(=YearEnd(Max(Date), -1))"}>}[Measure Field])

Hope this helps you.

Regards,

Jagan.

• ###### Re: Calculation YTD by month selected

Hi Jagan,

I want random months selection, example if i selected May14, then Previous Year: Sum Jun12-May13

• ###### Re: Calculation YTD by month selected

Hi,

If you want from Jan-13 - Dec-13, then use this

=sum({<Year=, Month=, Date={">=\$(=YearStart(Max(Date),-23))<=\$(=MonthEnd(Max(Date), -12))"}>}[Measure Field])

Hope this helps you.

If this not works come up with a sample file and expected output.

Regards,

Jagan.

• ###### 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..

• ###### Re: Calculation YTD by month selected

Hi Valdi,

Please close this thread by giving Correct and Helpful to the useful posts, this helps others in finding the answers for similar questions.

Regards,

Jagan.

• ###### Re: Calculation YTD by month selected

Hi Valdi,

This 'AsOf' Calendar will give you the flags you need to complete this and other types of date analysis.

http://community.qlik.com/docs/DOC-6593

Regards

Richard

QlikCentral.Com