Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
erric3210
Creator
Creator

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.

stalwar1gwassenaar

PFA.

Regards,

Erric

9 Replies
qlikviewwizard
Master II
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)

erric3210
Creator
Creator
Author

Hi Arjun,

Thanks for the reply.

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

erric3210
Creator
Creator
Author

Hi Sunny,

stalwar1

Can you please help me on this.

Regards,

Aviral Nag

sunny_talwar

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

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

Anonymous
Not applicable

! !Hi

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

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

table.PNG

shiveshsingh
Master
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.

sunny_talwar

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)

shiveshsingh
Master
Master

Yes, this should work