topic Re: Calculation YTD by month selected in QlikView

Calculation YTD by month selected

Thu, 12 Jun 2014 04:58:45 GMT

Hi Guys,

I need your help..

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)

Please help me..

thanks & regards..

Re: Calculation YTD by month selected

senpradip007 — Thu, 12 Jun 2014 05:06:18 GMT

Try somthig like in Expressions:

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

Re: Calculation YTD by month selected

Thu, 12 Jun 2014 05:54:26 GMT

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

senpradip007 — Thu, 12 Jun 2014 06:02:45 GMT

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

Thu, 12 Jun 2014 06:16:53 GMT

Hi Pradip

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

Thu, 12 Jun 2014 06:18:44 GMT

Hi Pradip,

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

senpradip007 — Thu, 12 Jun 2014 06:21:40 GMT

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

jagan — Thu, 12 Jun 2014 06:22:53 GMT

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

Thu, 12 Jun 2014 07:03:45 GMT

Hi Jagan,,

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

Re: Calculation YTD by month selected

senpradip007 — Thu, 12 Jun 2014 07:06:49 GMT

Can you share sample qvw?

Re: Calculation YTD by month selected

Thu, 12 Jun 2014 07:11:50 GMT

Well Done,

I've tried,

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

Re: Calculation YTD by month selected

jagan — Thu, 12 Jun 2014 07:13:16 GMT

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

Thu, 12 Jun 2014 07:24:34 GMT

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

VmaxYear = GetFieldSelections(Year) & GetFieldSelections(Month)

VminYear = Date(Addmonths(Date#(VmaxYear ,'YYYYMM'),-11),'YYYYMM')

Regards,

Prabhu

Re: Calculation YTD by month selected

senpradip007 — Thu, 12 Jun 2014 07:35:16 GMT

Can you share sample qvw?

Re: Calculation YTD by month selected

Thu, 12 Jun 2014 07:47:19 GMT

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

jagan — Thu, 12 Jun 2014 07:54:17 GMT

Yes Valdi.

Regards,

Jagan.

Re: Calculation YTD by month selected

Thu, 12 Jun 2014 09:48:03 GMT

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

jagan — Tue, 17 Jun 2014 05:49:53 GMT

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

Tue, 17 Jun 2014 06:31:23 GMT

Hi Jagan,

How about sum Previous Year?

Re: Calculation YTD by month selected

jagan — Tue, 17 Jun 2014 06:33:24 GMT

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.