topic Re: Running Total in App Development

Running Total

Anonymous — Mon, 06 Jun 2016 14:26:17 GMT

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

Re: Running Total

sunny_talwar — Mon, 06 Jun 2016 14:29:58 GMT

Try this:

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

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

Re: Running Total

Anonymous — Mon, 06 Jun 2016 14:38:15 GMT

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

Re: Running Total

Anonymous — Mon, 06 Jun 2016 14:59:10 GMT

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

Re: Running Total

sunny_talwar — Mon, 06 Jun 2016 15:03:31 GMT

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

Anonymous — Mon, 06 Jun 2016 15:15:52 GMT

Just a query, might be silly

In the Above Formula

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

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

sunny_talwar — Mon, 06 Jun 2016 15:20:17 GMT

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

Anonymous — Mon, 06 Jun 2016 15:38:09 GMT

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

Re: Running Total

sunny_talwar — Mon, 06 Jun 2016 15:38:51 GMT

I don't see any attachments. Can you check

Re: Running Total

Anonymous — Mon, 06 Jun 2016 15:59:54 GMT

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

Re: Running Total

sunny_talwar — Mon, 06 Jun 2016 17:39:49 GMT

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

Re: Running Total

Anonymous — Mon, 06 Jun 2016 18:55:58 GMT

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

sunny_talwar — Mon, 06 Jun 2016 19:09:41 GMT

Like this?

Expression:

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

Re: Running Total

Anonymous — Mon, 06 Jun 2016 19:28:07 GMT

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

sunny_talwar — Mon, 06 Jun 2016 19:30:17 GMT

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

Anonymous — Tue, 07 Jun 2016 12:12:27 GMT

Thanks Sir

I have few more queries. Kindly help me please

Regards

Deepanshu

Running Total with filters