Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ramkumar_manick
New Contributor III

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
Esteemed Contributor

Re: Rolling 12Months for Sales in Bar Chart

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

27 Replies

Re: Rolling 12Months for Sales in Bar Chart

MVP
MVP

Re: Rolling 12Months for Sales in Bar Chart

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
Esteemed Contributor

Re: Rolling 12Months for Sales in Bar Chart

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.

ramkumar_manick
New Contributor III

Re: Rolling 12Months for Sales in Bar Chart

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

YoussefBelloum
Esteemed Contributor

Re: Rolling 12Months for Sales in Bar Chart

Did you try it and it didnt work ?

OmarBenSalem
Esteemed Contributor

Re: Rolling 12Months for Sales in Bar Chart

I'd alter this part Youssef from:

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


to


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

ramkumar_manick
New Contributor III

Re: Rolling 12Months for Sales in Bar Chart

Yes Youssef, Didnt work...

YoussefBelloum
Esteemed Contributor

Re: Rolling 12Months for Sales in Bar Chart

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)

MVP
MVP

Re: Rolling 12Months for Sales in Bar Chart

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

Community Browser