Running Total

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

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

[sunny_talwar]

I don't see any attachments. Can you check

[Anonymous]

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