35 Replies Latest reply: Nov 13, 2017 9:59 AM by LUIGI ZANETTI

# Rolling sum on Qlik Sense

Hi all,

I need to build a "rolling sum" plot (or table as well), i. e. a sum that month by month runs on the latest 12 months.

More precisely, I have a graph that shows a variable (the number of sold products) month by month.

The other variable I want to show is the sum of sold products on one year up to that month.

The result should be a plot showing (month by month) the units sold in that month (e.g. March 2017) and the units sold on one year that far (e.g. April 2016 - March 2017).

I hope the request is clear.

Any help would be very appreciated.

Thanks, LuigiZ

• ###### Re: Rolling sum on Qlik Sense

Sum({<Num_Month = {\$(=Max(Num_Month)-1)}, Year = {\$(=Max(Year)-1)} >} Units_Sold)

Not a perfected expression cus by default what should be shown?

Current year sales or previous year sales?

And the selection should only happen if Month is selected?

if(getselectedcount(Month)>0,

Sum({<Num_Month = {\$(=Max(Num_Month)-1)}, Year = {\$(=Max(Year)-1)} >} Units_Sold))

Maybe this would work...? Num month should be a numeric field and year as well.

• ###### Re: Rolling sum on Qlik Sense

Hi Aehman,

the first formula you provided works but it gives the same result as:

Sum(Units_Sold)

What I wanted to do, instead, is to create  sum of Units_Sold on the last 12 months, month by month (the so called Rolling sum).

Something like this:

Sum(FROM Date.autoCalendar.MonthsAgo = 12 TO Date.autoCalendar.YearMonth)

But I don't know how to set these two ends of the sum

Thanks

• ###### Re: Rolling sum on Qlik Sense

Hi Luugi,

i hope that helps

Beck

• ###### Re: Rolling sum on Qlik Sense

Thanks Beck,

I'm not able to get you proposal: my table has 1 record with Quantity and Date fields, so I have a number in quantity for each day.

I'm looking for a Measure expression to put in the plot.

Thanks again

• ###### Re: Rolling sum on Qlik Sense

Complete information.

I created a table in the same app showing the same result of the plot, but in numeric way (both have the same issue).

My plot have

DIMENSION = Date.Auttocalendar.YearMonth

MEASURE = Sum(Quantity)

and it correctly shows me the total number of items sold per each month.

I'm really not able to show the units sold over 1 year:

Oct 2017 to show units sold from Nov,1 2016 to Oct, 31 2017

Nov 2017 to show units sold from Dec, 1 2016 to Nov, 30 2017

Dec 2017 to show units sold from Jan, 1 2017 to Dec, 31 2017

My fields are DATE for the dates and QUANTITY for the number of units

• ###### Re: Rolling sum on Qlik Sense

I'm really not able to show the units sold over 1 year:

Oct 2017 to show units sold from Nov,1 2016 to Oct, 31 2017

Nov 2017 to show units sold from Dec, 1 2016 to Nov, 30 2017

Dec 2017 to show units sold from Jan, 1 2017 to Dec, 31 2017

You want to how previous year sales upon selection of a particular field or by default?

You can create a Last Year Flag in back end, use that Flag =1 in expression to show the sales of PYTD.

Maybe I'm wrong, this looks like rolling year with month

Can you post a sample data?

Thanks

• ###### Re: Rolling sum on Qlik Sense

@Aehman

I want the sales over 12 month, up to the indexed month by default. The, if I select a product, he plot should show sales restricted to that product.

It's neither about sales of PYTD, nor sales YTD, it's sales on 12 months backwards.

A sample data looks like this:

 Quantity Posting Date Product 3 31/07/2017 prod A 20 28/07/2017 prod A 50 28/07/2017 prod A 4 28/07/2017 prod A 9 27/07/2017 prod A 7 26/07/2017 prod B 3 26/07/2017 prod B 16 26/07/2017 prod B 4 24/07/2017 prod B 30 21/07/2017 prod C 5 21/07/2017 prod C 5 19/07/2017 prod C 7 18/07/2017 prod C 54 18/07/2017 prod A 6 18/07/2017 prod A 1 18/07/2017 prod A 1 18/07/2017 prod B 2 04/07/2017 prod B 1 21/07/2017 prod C 1 13/07/2017 prod C 75 29/07/2017 prod D 40 28/07/2017 prod D 5 28/07/2017 prod D 5 28/07/2017 prod D 6 27/07/2017 prod D 1 26/07/2017 prod B 3 26/07/2017 prod B 5 26/07/2017 prod B

for monthly sold product:

DIMENSION: PostingDate.autoCalendar.YearMonth

MEASURE: Sum(Quantity)

I need to create a measure for the "Products on market" sold over 12 months backwards, month by month:

• ###### Re: Rolling sum on Qlik Sense

Create your own master calendar: (it's always better to build ur own calendar, it's a preference thing)

Just copy and paste this:

Let vMinDate=num(MakeDate(2014,12,31));

Let vMaxDate=Today()+30;

NoConcatenate

[D_Calendar_TMP]:

Load date(\$(vMinDate)+ RowNo()) as [Calendar date]

AutoGenerate \$(vMaxDate)-\$(vMinDate);

NoConcatenate

[D_Calendar_TMP2]:

,AutoNumber(date([Calendar date], 'DD/MM/YYYY'),'%Date ID') as [%Date ID]

,date([Calendar date], 'DD/MM/YYYY') as [Calendar date]

,Day([Calendar date]) as [Day of date]

,'W' & right(WeekName([Calendar date],0,-4), 2) as [week date]

,'Q' &  Num(ceil(month([Calendar date])/3), '00') as [quarter date]

, right(WeekName([Calendar date],0,-4), 2) as [Week of date]

,Month([Calendar date]) as [Month of date]

,QuarterName([Calendar date]) as [Quarter of date]

,Year([Calendar date]) as [Year of date]

, right(WeekName([Calendar date],0,-4), 2) &'/'&Year([Calendar date]) as [Year week date]

,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date]

,num( Year([Calendar date])   ) & '- Q' &  Num(ceil(month([Calendar date])/3), '00') as [Year quarter date]

,WeekStart([Calendar date],0,-4) as [Week start date]

,WeekEnd([Calendar date],0,-4) as [Week end date]

,MonthStart([Calendar date]) as [Month start date]

,MonthEnd([Calendar date]) as [Month end date]

,QuarterStart([Calendar date]) as [Quarter start date]

,QuarterEnd([Calendar date]) as [Quarter end date]

,YearStart([Calendar date]) as [Year start date]

,YearEnd([Calendar date]) as [Year end date]

,AutoNumber(WeekEnd([Calendar date],0,-4) , '%Year week SEQ') as [%Year week SEQ]

,AutoNumber(Num(Month([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year month SEQ') as [%Year month SEQ]

,AutoNumber(Num(QuarterName([Calendar date]), '00')&'/'&Year([Calendar date]), '%Year quarter SEQ') as [%Year quarter SEQ]

Resident D_Calendar_TMP;

join(D_Calendar_TMP2)

Load max([%Date ID]) as [%Date ID]

,1 as [Week end flag]

Resident D_Calendar_TMP2

Group by [Week end date];

join(D_Calendar_TMP2)

Load max([%Date ID]) as [%Date ID]

,1 as [Month end flag]

Resident D_Calendar_TMP2

Group by [Month end date];

join(D_Calendar_TMP2)

Load max([%Date ID]) as [%Date ID]

,1 as [Quarter end flag]

Resident D_Calendar_TMP2

Group by [Quarter end date];

join(D_Calendar_TMP2)

Load max([%Date ID]) as [%Date ID]

,1 as [Year end flag]

Resident D_Calendar_TMP2

Group by [Year end date];

join(D_Calendar_TMP2)

Load min([%Date ID]) as [%Date ID]

,1 as [Week start flag]

Resident D_Calendar_TMP2

Group by [Week start date];

join(D_Calendar_TMP2)

Load min([%Date ID]) as [%Date ID]

,1 as [Month start flag]

Resident D_Calendar_TMP2

Group by [Month start date];

join(D_Calendar_TMP2)

Load min([%Date ID]) as [%Date ID]

,1 as [Quarter start flag]

Resident D_Calendar_TMP2

Group by [Quarter start date];

join(D_Calendar_TMP2)

Load min([%Date ID]) as [%Date ID]

,1 as [Year start flag]

Resident D_Calendar_TMP2

Group by [Year start date];

NoConcatenate

D_Calendar:

,[%Date ID]

,[Calendar date]

,[Day of date]

,[Week of date]

,[Month of date]

,[Quarter of date]

,[Year of date]

,[Year week date]

,[Year month date]

,[week date]

,[quarter date]

,[Year quarter date]

,[Week start date]

,[Week end date]

,[Month start date]

,[Month end date]

,[Quarter start date]

,[Quarter end date]

,[Year start date]

,[Year end date]

,[%Year week SEQ]

,[%Year month SEQ]

,[%Year quarter SEQ]

,[Month Fact]

,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Month start date],'DD/MM/YYYY')) + 1) AS [Month Diff]

,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Quarter start date],'DD/MM/YYYY')) + 1) AS [Quarter Diff]

,Num ( (date([Calendar date],'DD/MM/YYYY') - date([Year start date],'DD/MM/YYYY')) + 1)  AS [Year Diff]

,if(WeekEnd([Calendar date])=WeekEnd(Date(Today()-1,'DD/MM/YYYY')),Date(Today()-1,'DD/MM/YYYY'), WeekEnd([Calendar date])) as [Week end date 2]

,if(MonthEnd([Calendar date])=MonthEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(MonthEnd([Calendar date]),'MMM YYYY') ) as [Month end date 2]

,if(QuarterEnd([Calendar date])=QuarterEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(QuarterEnd([Calendar date]),'MMM YYYY') ) as [Quarter end date 2]

,if(YearEnd([Calendar date])=YearEnd(Date(Today()-1,'DD/MM/YYYY')),date(Date(Today()-1,'DD/MM/YYYY'),'MMM YYYY'), date(YearEnd([Calendar date]),'MMM YYYY') ) as [Year end date 2]

//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Week end flag]) as [Week end flag]

, if(date(floor( WeekEnd([Calendar date],0,-4)))=date(floor(WeekEnd(Today()-1,0,-4))),

if([Calendar date]=Date( '\$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Week end flag]) as [Week end flag]

,if(date(floor( MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),

if([Calendar date]=Date( '\$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month end flag]) as [Month end flag]

,if( date(floor(QuarterEnd([Calendar date])))=date(floor(QuarterEnd(Date(Today()-1,'DD/MM/YYYY')))),

if( [Calendar date]=Date('\$(vCheckRevenuDate)','DD/MM/YYYY'),1) ,[Quarter end flag] )  as [Quarter end flag]

//,if([Calendar date]=Date(Today()-1,'DD/MM/YYYY'),1,[Year end flag]) as [Year end flag]

, if(date(floor( YearEnd([Calendar date])))=date(floor(YearEnd(Date(Today()-1,'DD/MM/YYYY')))),

if([Calendar date]=Date( '\$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Year end flag]) as [Year end flag]

, [Week start flag]

, [Month start flag]

, [Quarter start flag]

, [Year start flag]

// ,if(date(floor(MonthEnd([Calendar date])))=date(floor(MonthEnd(Date(Today()-1,'DD/MM/YYYY')))),

// if([Calendar date]=Date('\$(vCheckRevenuDate)','DD/MM/YYYY'),1),[Month start flag]  )  as [Month start flag]

,AutoNumber(Num(Month([Month Fact]), '00')&'/'&Year([Month Fact]), '%Year month fact SEQ') as [%Year month fact SEQ]

Resident D_Calendar_TMP2;

Drop table D_Calendar_TMP, D_Calendar_TMP2;

DONT't change a thing

Then, in your fact table, in which you have the date field; do as follow:

AutoNumber(date(YourDateField, 'DD/MM/YYYY'),'%Date ID') as [%Date ID]

Now: as an expression for ur rolling month:

use this:

sum({<[Calendar date]=,[Month of date]=, [Year of date]=, [%Year month SEQ] = {"> \$(= max([%Year month SEQ]) - 12)  <= \$(=[%Year month SEQ])"}>}QUANTITY)

and a dimension use :

date([Month end date 2], 'MMM-YYYY')

Now, when you choose a Mars 2017 for example, you'll have:

• ###### Re: Rolling sum on Qlik Sense

If you want cumulative rolling: please refer to this:

Calculating rolling n-period totals, averages or other aggregations

and try to use this:

sum( aggr( rangesum( above( sum(QUANTITY),0,12) ),Month))

• ###### Re: Rolling sum on Qlik Sense

@omar bensalem, the "Calculating rolling n-period totals, averages or aggregations" looks fine, I read it earlier, but it's referred to Qlik View. How do I translae it to Qlik Sense?

all the proposed formulas don't work

• ###### Re: Rolling sum on Qlik Sense

The example above has been implemented with Qlik Sense; (the example with my proper master calendar)

You should have tested it... I even attached an image to show you it does what you want to achieve

• ###### Re: Rolling sum on Qlik Sense

Ok, I didn't test it because I don't know where to paste alla that code.

• ###### Re: Rolling sum on Qlik Sense

Or try sthing like:

sum({<Date.Autocalendar.YearMonth=,Date.Autocalendar.Year=,Date.Autocalendar.Month=,Date.Autocalendar.Date=,

• ###### Re: Rolling sum on Qlik Sense

YTD, MTD issue

where I tried to explain step by step some of the basc time functions in Qlik !

Omar BEN SALEM

• ###### Re: Rolling sum on Qlik Sense

omarbensalem I read your explanation on syntax and it made a lot of light, thank you very much.

unfortunately, I'm still not able to get what I need.

• ###### Re: Rolling sum on Qlik Sense

Hi Luigi,

For your request you should use below expression:

what above for time period: nov '16 - oct '17

• ###### Re: Rolling sum on Qlik Sense

Hi Andrea, I saw your reply, it looks fine, but it gives me all 0 as a result.

The same result I get from

sum({<Date.Autocalendar.YearMonth=,Date.Autocalendar.Year=,Date.Autocalendar.Month=,Date.Autocalendar.Date=,

proposed by Omar.

One problem I realized I had, in the meanwhile, was: my field's name was "Posting Date" so in uploading to QS it became "Posting.Date" this made confusion I guess (because Date was recognized as a function).

I changed it to PostingDate

• ###### Re: Rolling sum on Qlik Sense

Hi Luigi,

At this point I think the quickest way to fix it is posting a sample qvf file.

• ###### Re: Rolling sum on Qlik Sense

How can I get it?

I'm using QS Desktop

• ###### Re: Rolling sum on Qlik Sense

you'll find the qvf file in C:\Users\your_windows_account\Documents\Qlik\Sense\Apps

• ###### Re: Rolling sum on Qlik Sense

Does it pass even the data?

(they are company sensitive, and it's better for me not to pass them)

• ###### Re: Rolling sum on Qlik Sense

Create sample app with sample data

• ###### Re: Rolling sum on Qlik Sense

ok I suggest you to create an excel file with all data you are using within your chart object and next create a new app reading that excel file.

• ###### Re: Rolling sum on Qlik Sense

I C&P the app's file, then changed the base csv file

• ###### Re: Rolling sum on Qlik Sense

which is the chart object involved?

• ###### Re: Rolling sum on Qlik Sense

you get 0 as result because you have data only for 2011 year, at least for the app you sent.

• ###### Re: Rolling sum on Qlik Sense

Sorry, yesteday I was in a hurry and made some mess.

Here is the relevant part of my app, with data on a wider timeframe.

The interested table is "Rolling sum table" (where I tried to add a 3 months rolling sum), and Rolling sum plot.

The result of the suggested formula gives me simply  the sum over the month. I can't find what's wrong.

• ###### Re: Rolling sum on Qlik Sense

Here's ur expression :

sum({< [PostingDate.autoCalendar.YearMonth]=,[PostingDate.autoCalendar.Year]=,[PostingDate.autoCalendar.Month]=,[PostingDate.autoCalendar.Date]=,

result:

• ###### Re: Rolling sum on Qlik Sense

check the attached app:

• ###### Re: Rolling sum on Qlik Sense

Hi Omar,

I understand the editing you made to the formul and it makes sense, but if you plot its result aside of a simple monthly sum, you will see that it's not correct (there are a lot of zeros, then on the last 12 posistions a value tht equals the monthly total).

I attach your app with this visualisation. and removed some items on the dashboard.

• ###### Re: Rolling sum on Qlik Sense

Hi Luigi,

Try with this expression below:

Sum(

Aggr(

RangeSum(

Above( total  Sum( {< [PostingDate.autoCalendar.YearMonth] = >} Quantity)

, 0,3 )

)

,[PostingDate.autoCalendar.YearMonth] )

)

However I suggest you to take a look at Calculating rolling n-period totals, averages or other aggregations

• ###### Re: Rolling sum on Qlik Sense

Tank you agigliotti!

I've read that article, as it was advised earlier by omarbensalem, but couldn't transpose it in my case.

I did some try as well, but with no result.

The formula you suggest gives a total, but it's not the right one (it makes a sum but I don't understand the calculus).

So I tried to remove the outest "Sum( Aggr(" and it works fine    (see attached file).

So the right formula, for my case, is:

RangeSum(

Above( total Sum( {<[PostingDate.autoCalendar.YearMonth] = >} Quantity)

,           0, 3)

)

This makes a 3 months Rolling sum.

By wrinting 12 instead of 3 I make 12 months rolling sum.

Great thank you to all who contributed.

• ###### Re: Rolling sum on Qlik Sense

just to know the expression i gave you should be changed a little as below:

Sum(

Aggr(

RangeSum(

Above( Sum([Sum(Quantity)])

, 0, 3)

)

, (PostingDate,(NUMERIC, ASCENDING)) )

)

this returns the same result than your formula plus the total sum.

• ###### Re: Rolling sum on Qlik Sense

That's right Andrea.

this formula gives the total as well, but still the values aren't correct.