15 Replies Latest reply: Jun 7, 2016 8:12 AM by Deepanshu Chamoli

# Running Total

Hello All

Please tell me if it is possible or not in Qlik Sense.

Need your help on urgent basis

For calculating the running total I am using the below formula. It works perfectly fine for me

rangesum(above(TOTAL sum(Cases),0,rowno(TOTAL)))

The issue is that when I choose the particular month it shows only Current Month data. I want if the person filter out any month it sum all the data till current month

For E.g. (Attached Snapshot)

Suppose if I choose/Filter March 2014 it shows total 60. Currently it shows only 20

Regards

Deepanshu

• ###### Re: Running Total

Try this:

RangeSum(Above(TOTAL Sum({<Month, Date, Year, MonthYear>}Cases),0,rowno(TOTAL))) * Avg(1)

or

RangeSum(Above(TOTAL Sum({1}Cases),0,rowno(TOTAL))) * Avg(1)

• ###### Re: Running Total

Thanks Sunny!!

The Above 2nd Formula works in the table but if I use it as a KPI, it takes the Total value for all the months. Not for the particular month

Thanks again

• ###### Re: Running Total

For the KPI, you can try this:

Sum({<Date = {"\$(='<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, Month, Year, Quarter, MonthYear>} Cases)

If this doesn't work, would you be able to share a sample?

• ###### Re: Running Total

Just a query, might be silly

In the Above Formula

RangeSum(Above(TOTAL Sum({<Month, Date, Year, MonthYear>}Cases),0,rowno(TOTAL))) * Avg(1)

OR

Sum({<Date = {"\$(='<=' & Date(Max(Date), 'DateFieldFormatHere'))"}, Month, Year, Quarter, MonthYear>} Cases)

1. Date(Max(Date) do I need to put the Name of my column here like DATE(MAX(Active_Date))

2. I do not have in built function for Year & Month Year

3. Do I need to use the 2nd formula with Range Sum formula

• ###### Re: Running Total

1) Yes use your Active_Date to replace the Date

Sum({<Date = {"\$(='<=' & Date(Max(Active_Date), 'DateFieldFormatHere'))"}, Month, Year, Quarter, MonthYear>} Cases)

2) Don't use those fields if those are not there in your application. Use all those fields where you might make a selection and don't want them to impact the expression's output (may be all date related fields

3) 1st expression is for a straight table/pivot table where Date/Month is used  as a dimension. 2nd expression is good for a text box object.

• ###### Re: Running Total

Thanks a lot Sunny for the quick reply

It is not working for me.. I miss something.

I have attached my excel sheet, kindly look at it and advise the formula.

I only have the Month and Date as a function in Qlik Sense

• ###### Re: Running Total

I don't see any attachments. Can you check

• ###### Re: Running Total

I have attached the sheet

Please let me know if it is visible or not

I want the KPI as Cases/Running Total based on filter Month

Thanks!!!

• ###### Re: Running Total

What is the expected output from this sample you have provided?

• ###### Re: Running Total

Thanks sir for spending your time in resolving my query.

I have only three column in my actual data set... Month, Count and Cases

I have calculated the running total based on your formula in Qlik sense Visualization and it is working fine. If I select any particular month it sums all the previous month values in the current month.

Hence in the straight table I am able to calculate the % as ( Cases for particular month)/ Running total of particular month.

But if apply the same formula in KPI, it is not taking the running total. Instead of that it picks the particular month count which is causing the issue..

I want a formula which picks the running total in denominator for KPI so that I can able to calculate the % of particular month

Please let me know if it make any sense to you...

• ###### Re: Running Total

Like this?

Expression:

Sum(Cases)/Sum({<Active_Date = {"\$(='<=' & Date(Max(Active_Date), 'M/D/YYYY'))"}>} Counts)

• ###### Re: Running Total

Yes sir.. This is exactly what I want... I must say u r genius.

Just one more query.. Is it possible to calculate the % for a complete multiple months simultaneously..

• ###### Re: Running Total

Not sure what you mean when you say this -> Is it possible to calculate the % for a complete multiple months simultaneously

Can you elaborate please?

• ###### Re: Running Total

Hi,

My YTD(Year till date) works with the below formula:

Sum({<Month={\$(=Max(Month))},Version = {"Actual 2016"}>}SumOfBudget_Amount)

This is for 2016 only and I have data till marc 2016. From the month filter if I choose it gives me till jan,feb march..

if I choose feb it givevs me jan and feb...

Thanks