Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

deepanshu_chamo1
New Contributor

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

Please advise

Regards

Deepanshu

 

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Running Total

Like this?

Capture.PNG

Expression:

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

15 Replies
MVP
MVP

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)

gompa786
Contributor III

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

deepanshu_chamo1
New Contributor

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

Please advise

Thanks again

MVP
MVP

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?

deepanshu_chamo1
New Contributor

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

MVP
MVP

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.

deepanshu_chamo1
New Contributor

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

Please suggest

MVP
MVP

Re: Running Total

I don't see any attachments. Can you check

deepanshu_chamo1
New Contributor

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

Please suggest

Thanks!!!