Help me in to the rolling 12 months

[#1]

Hello Everyone,

Please help me into the rolling 12 months. with attached excel and qvd

MY requirement is below:

i have month wise revenue data for three years, 2012, 2013 and 2014

if i select year it should show the 12 months of change (2014 revenue/2013 revenue) of selected year.

dimension is month-year

if i select month and year, for example if i select jun and 2014, it should show previous 12 months data, means from jun 2014 to july 2013

here for july 2013 the change is July 2013/July2012 value should show.

Change i cannot calculate separately. i should calculate in the expression only

Please help me to achieve this.

Big thanks in advance

[MK_QSL]

Like this?

[#3]

Hello Manish,

Thanks for the reply,

1) i don't have Year_Month as filter,(when i select the year and month it should change accordingly)

2) for single value it is fine, i wanted to calculate the change between two years. The formula is like below

=SUM({<Year={2014},YearMonth = {'>=$(=(Max(YearMonth)-11))<=$(=Max(YearMonth) )'}>}Change("currentyear/Previous Year))

/

=SUM({<Year={2013}YearMonth = {'>=$(=(Max(YearMonth)-11))<=$(=Max(YearMonth) )'}>}Change(PreviousYear/Previous to Previous Year))

Please help into this

Thanks

[MK_QSL]

I think you are getting Year and Month from your date field... If yes, you can use this date field to create YearMonth....

[#5]

I can create based on the date, but user do not want that way.

and also please help me in the change calculation.

Thanks

[MK_QSL]

Can I have the Logic explanation behind your expression? I would create accordingly

[#7]

Formula that i am using now:

it is giving the correct value but for the dimension it is not working

=num((((((sum({$<Year_No={$(vCY)},Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'}>}aggr(sum({$<Year_No={$(vCY)}  , Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'} >}Revenue1),Code)))/

((sum({$<Year_No={$(vCY)} , Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'}>}No_Of_Rooms))*(sum({$<Year_No={$(vCY)},Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'}  >}No_Of_Days))))

/

((sum({$<Year_No={$(vCY)},Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'}>}aggr(sum({$<Year_No={$(vCY)}  , Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'} >}Revenue2),Code)))/

((sum({$<Year_No={$(vCY)} , Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'}>}Avail_Rooms))*(sum({$<Year_No={$(vCY)}  , Month_Year={'>=$(vMaxMonthYear_PY12) <=$(vMaxYearMonth)'} >}No_Of_Days)))))

/

(((sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}aggr(sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'} >}Revenue1),Code)))/

((sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}No_Of_Rooms))*(sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}No_Of_Days))))

/

((sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}aggr(sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'} >}Revenue2),Code)))/

((sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}Avail_Rooms))*(sum({$<Year_No={$(vPY)},Month_Year={'>=$(Variable1) <=$(vMaxPYearMonth)'}>}No_Of_Days))))))-1),'#,##0.00%')

************************************************************

vMaxMonthYear_PY12 : =date(AddMonths(vMaxYearMonth,-11),'MM/DD/YYYY')

vMaxYearMonth=max({<Flag={'D360'}>} Date( Date# ( Month&'-'&Year_No,'MMM-YYYY'),'MMM-YYYY'))

Variable1=date(AddMonths(vMaxPYearMonth,-11),'MM/DD/YYYY')

vMaxPYearMonth==date#(max({<Flag={'D360'}>} Date(Date#(Month,'MMM'),'MMM'))&'-'&(Max({<Flag={'D360'}>}date(Year_No-1, 'YYYY'))),'MMM-YYYY')

[#8]

Can anybody look into this please,

[#9]

No Reply

[#10]

Hi,

If you want to achieve this with out using master calendar

Introduce a fiscal year Colum in the spread sheet and load fiscal year to qlikview.

and look at the attached QV