# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Contributor III

## Rolling 12 Months Logic Required for every Month in Pivot Chart

Hi All,

I need help in calculating Rolling 12 Months Logic for every Month in Pivot Chart.

I've explained my requirement in Attached Excel.

Maybe someone has faced this issue earlier. Would Appreciate if someone provide solution to this.

PFA.

Regards,

Erric

9 Replies
Master II

Hi Check this.

Sheet1:

LOAD *,Date(MonthTemp,'MMM-YY') as Month,Year(MonthTemp) as Year;

LOAD [New Month Year] as MonthTemp,

Amount_1 as Amount,

[Desired Output]

FROM

rolling.xlsx

(ooxml, embedded labels, table is Sheet1);

Use this:

=SUM({<Year,Month = {">=\$(=Date(MonthStart(Max(Month),-11),'MMM-YY'))<=\$(=Date(MonthStart(Max(Month)),'MMM-YY'))"}>}Amount)

Master II
Contributor III
Author

Hi Arjun,

I actually tried something like that but its not working:

Sum({<[New Month Year] ={"\$(='>=' & Date(AddMonths(Max([New Month Year]), -11), 'MMM-YY') & '<=' & Date(Max([New Month Year]), 'MMM-YY'))"}>} TOTAL Amount_1)

Also, the Logic shared by you is not working.

Regards,

Erric

Contributor III
Author

Hi Sunny,

Regards,

Aviral Nag

MVP

I recommend using The As-Of Table‌ if you are able to modify the script... or this

RangeSum(Above(Sum(Amount_1), 0, 12))

Partner

! !Hi

please find the attached app i got  answer using strait table like below

=RangeSum( Below(sum(Amount_1), 0,12 ))

Master

May be this expression

RangeSum(above(sum(Amount_1),0,12))

But if you select any monthyear in your listbox, then this will give only that monthyear amount.

MVP
 But if you select any monthyear in your listbox, then this will give only that monthyear amount.

This can be addressed using this

RangeSum(Above(Sum({<MonthYear>}Amount_1), 0, 12)) * Avg(1)

Master

Yes, this should work