Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
brucejensen
Contributor III
Contributor III

Rolling 12 months in Chart

Can someone tell me how to get my chart to only display a rolling 12 month period? I have a master calendar but I can't get the set analysis to work. I'm not sure which pieces of the master calendar I should be using to come up with the rolling 12 mos. My date field is called [Month Reviewed] i.e. Mar-15 for March 2015.

Any help would be greatly appreciated

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Change according to your FieldName from my below example...

Create below field in your script..

Date(MonthStart(TempDate),'MMM-YY') as InvoiceMonthYear,

Last 12 Months Sales From Current Month

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

  InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

  InvoiceMonthYear = {">=$(=Date(MonthStart(Today(),-11),'MMM-YY'))<=$(=Date(MonthStart(Today()),'MMM-YY'))"}

  >}Sales)

Last 12 Months Sales From User Selected MonthYear

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

  InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

  InvoiceMonthYear = {">=$(=Date(MonthStart(Max(InvoiceMonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(InvoiceMonthYear)),'MMM-YY'))"}

  >}Sales)

View solution in original post

13 Replies
awhitfield
Partner
Partner

Hi Bruce,

You could use Dimension limits against your date field, e.g. tick Show only = 12, un-tick show  others

Regards

Andy

brucejensen
Contributor III
Contributor III
Author

Thanks. I did that and for some reason the chart keeps Jan-14, Feb-14, and Mar-14 and drops Jan-15 and Feb-15?

awhitfield
Partner
Partner

Interesting, may be a sort order issue Sep-14 is missing 2 in both screen shots. Any chance you can post the qvw?

Andy

MK_QSL
MVP
MVP

Change according to your FieldName from my below example...

Create below field in your script..

Date(MonthStart(TempDate),'MMM-YY') as InvoiceMonthYear,

Last 12 Months Sales From Current Month

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

  InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

  InvoiceMonthYear = {">=$(=Date(MonthStart(Today(),-11),'MMM-YY'))<=$(=Date(MonthStart(Today()),'MMM-YY'))"}

  >}Sales)

Last 12 Months Sales From User Selected MonthYear

Create a Bar Chart

Dimension

InvoiceMonthYear

Expression

=SUM({<

  InvoiceDate=,InvoiceDay=,InvoiceMonth=,InvoiceQuarter=,InvoiceWeekDay=,InvoiceYear=,

  InvoiceMonthYear = {">=$(=Date(MonthStart(Max(InvoiceMonthYear),-11),'MMM-YY'))<=$(=Date(MonthStart(Max(InvoiceMonthYear)),'MMM-YY'))"}

  >}Sales)

View solution in original post

brucejensen
Contributor III
Contributor III
Author

Actually, we don't have any Sep-14 data so that's why that month is missing. And, no unfortunately I can't post the .qvw..

nagaiank
Specialist III
Specialist III

A syntax for rolling 12 month set expression is in the blog The Magic of Set Analysis – Point In Time Reporting | iQlik - Everything QlikView.

Hope this helps.

awhitfield
Partner
Partner

1. How are you sorting the axis?

2. Can you post the Calendar Script?

Andy

awhitfield
Partner
Partner

Arggh, just re read what I put earlier - total garbage!

Instead, how a looks at the attached link which does the job properly

https://community.qlik.com/message/398262#398262

Humbly!

Andy

brucejensen
Contributor III
Contributor III
Author

Works perfectly. Thank you so much!