topic Re: Rolling 12Months for Sales in Bar Chart in Visualization and Usability

Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 09:09:32 GMT

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

Re: Rolling 12Months for Sales in Bar Chart

MK_QSL — Fri, 23 Mar 2018 09:11:39 GMT

Calculating rolling n-period totals, averages or other aggregations

Re: Rolling 12Months for Sales in Bar Chart

stevedark — Fri, 23 Mar 2018 09:23:41 GMT

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

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 09:29:30 GMT

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.

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 09:52:31 GMT

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

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 09:55:20 GMT

Did you try it and it didnt work ?

Re: Rolling 12Months for Sales in Bar Chart

OmarBenSalem — Fri, 23 Mar 2018 09:58:21 GMT

I'd alter this part Youssef from:

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

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

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 10:22:36 GMT

Yes Youssef, Didnt work...

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 10:26:47 GMT

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)

Re: Rolling 12Months for Sales in Bar Chart

stevedark — Fri, 23 Mar 2018 10:29:10 GMT

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

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 10:29:52 GMT

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

PS: Make sure you use the correct field name (maybe it is not named like YYYYMM)

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 10:34:46 GMT

I am limiting last 12 months only...

X-Axis(Dimension) = CLAIM_YEARMONTH - Format like YYYYMM (Eg: 201701, 201702,.. 201707 .. 201712, 201801, 201802)

Y-Axis(Measure) = TOTAL_EXPENSES - $

If is format case, how to set my yearmonth analysis? Becuase there is no date available in the data.. All data's pretending by year & month.. Like YYYYMM

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 10:38:30 GMT

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

Re: Rolling 12Months for Sales in Bar Chart

stevedark — Fri, 23 Mar 2018 10:38:47 GMT

You will therefore be wanting something like this in the expression:

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

Steve

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 10:44:31 GMT

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

Why these are in Green color...? while pasting in Expression....

i think i need to make set format for yearmonth like "YYYYMM"? If yes, how should i do it?

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 10:51:23 GMT

My bad,

like steve dark said it will be this:

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

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 11:00:43 GMT

No Youssef... Again its showing all the months... not limiting to last 12 months...

Re: Rolling 12Months for Sales in Bar Chart

stevedark — Fri, 23 Mar 2018 11:40:06 GMT

Note you have referred to both TOTAL_EXPENSE and TOTAL_EXPENSES, you will need to use the right name.

It's green because it is set analysis, this is just how Sense formats set analysis.

If CLAIM_YEARMONTH is not actually a date field, rather it's a string or a number, then it will need to be treated differently.

If you create a Filter Pane with CLAIM_YEARMONTH, does it align to the right or the left?

You may be able to get the expression to work with the following:

Sum( {<CLAIM_YEARMONTH={">=$(=Max(CLAIM_YEARMONTH)-100)"}>} TOTAL_EXPENSES)

The best thing to do is to test and then build up, for example, does this work:

Sum( {<CLAIM_YEARMONTH={">=201703"}>} TOTAL_EXPENSES)

If so, we just need to get the date value to work correctly. Next create a text box and check the contents of the variable (the bit inside the $() ) to work, add the following as a measure:

=Max(CLAIM_YEARMONTH)-100

Check whether that returns the month you are expecting.

It will be far better to format the date properly on load (using Date and Date#) but treating the date as an integer should work.

Steve

Re: Rolling 12Months for Sales in Bar Chart

YoussefBelloum — Fri, 23 Mar 2018 12:50:19 GMT

would you be able to attach some data here ? at least more than 12 rows corresponding to year using a YYYYMM field

Re: Rolling 12Months for Sales in Bar Chart

Anonymous — Fri, 23 Mar 2018 13:25:47 GMT

Here i attached raw data which 201601 to 201802... But i need to show last 12 rolling months... Eg: 201703 to 201802...