Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

How can I do running total for the previous 12 months for each month in a straight table?

Hello!

I have a straight table like this in my data:

What I would like to show is running total for each month based on the previous 12 months.

For example, in the above example I want to show 104,603 in the Dec 2014 Turnover column.

I have two Dimensions above (Company and Month Year).

Does anyone know how to do this please? If there are no previous months data it should say 'No previous 12 months data'.

Any help would be great

3 Replies
sinanozdemir
Specialist III
Specialist III

Hi John,

Please see the below screenshot:

Capture.PNG

Hope this helps.

Thanks

jblomqvist
Specialist
Specialist
Author

Hi Sinan,

Thanks for replying back. Your answer is partially true. What should happen is it should show running totals. I.e. for November it should show the 12 months before added up all together.

Any idea how I can do that?

I managed to get this working:

sum(aggr(rangesum( above($(Turnover),0,12) ),Company,MonthYear))

However this expression starts responding to selections (i.e. if I select December 2014 it shows the total as 12,490 as per my original screenshot and not the total for 12 months Turnover added up together.

Anyone have idea how to get it to that?

sinanozdemir
Specialist III
Specialist III

You mean like this:

I named your expression as John's Expression

Capture2.PNG

When you make a selection, it only shows the running totals for that month:

It kept the November running total.

Capture3.PNG

And the expression is:

Capture.PNG

In my original expression, you can put {1} in the aggregations:

Capture.PNG

Hope this helps.