Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Like this?
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
I think you are getting Year and Month from your date field... If yes, you can use this date field to create YearMonth....
I can create based on the date, but user do not want that way.
and also please help me in the change calculation.
Thanks
Can I have the Logic explanation behind your expression? I would create accordingly
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')
Can anybody look into this please,
No Reply
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