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: 
Anonymous
Not applicable

Rolling 12Months for Sales in Bar Chart

Hello Team,

I am trying to show only last 12 months Total_Expense measure value in bar chart... X-Axis = YYYYMM & Y-Axis= Sum of Total Expense.

I am using Sum(TOTAL_EXPENSE) expression in measure value..


Format is YYYYMM, which data used to.

Can any one help me find out expression which i can use for last 12 rolling months in the bar chart..

Untitled.png

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

No, this one is not correct becausse it is not complete..

If you already opened the QVW I send above and the output is fine for you, just use this:

=Sum(  {<CLAIM_YEARMONTH={">=$(=Date(AddMonths(Max(CLAIM_YEARMONTH),-12),'YYYYMM'))<=$(=Max(CLAIM_YEARMONTH))"}>}  TOTAL_EXPENSE)

PS: don't forget to take a look at the script to see how I altered the loading of the CLAIM_YEARMONTH field, you will have to do the same in your script.

hope it is clear

View solution in original post

27 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

The way I tend to deal with rolling values is to build a table which links each month to itself and the previous 11 months and then use that table as the dimension.

This blog post explains both why this tends to work best, and how to implement it:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

The blog is a few years old and based on QlikView, but the data model part is identical.  If you download the QVW example file you can use the QlikView Converter in the Qlik Sense Dev Hub to bring it into Sense.

Hope that helps.

Steve

YoussefBelloum
Champion
Champion

Hi,

try this:

Sum(  {<YYYYMM={">=$(=AddMonths(Max(YYYYMM),-12))<=$(=Max(YYYYMM))"}>}  TOTAL_EXPENSE)


If it don't work, you should evaluate and format your YYYYMM field as a Date, using Date#() and Date() functions.

Anonymous
Not applicable
Author

How should i evaluate and format... My X-Axis is like "201701(YYYYMM), 201702, .... " Format dimensions, can you help on this?

YoussefBelloum
Champion
Champion

Did you try it and it didnt work ?

OmarBenSalem

I'd alter this part Youssef from:

$(=AddMonths(Max(YYYYMM),-12))


to


$(=MonthStart(AddMonths(Max(YYYYMM),-12)))

Anonymous
Not applicable
Author

Yes Youssef, Didnt work...

YoussefBelloum
Champion
Champion

I didn't use the Monthstart because it is YYYYMM field.. don't see the utility of this unless I use a Date field (with day included)

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Just to confirm, are you trying to do a 12 Month Moving Annual Total or just limit the bar chart to the last 12 months?

If it is the former then the blog post above will deliver what you want.

If it is just limiting to the last 12 months that is different.

What is the field name of the dimension?  I'm presuming that YYYYMM is the date format and not the name of the field?  In the examples given above the field name should be used in place of YYYYMM.  You also need to be careful of date formats in Set Analysis.

Steve