21 Replies Latest reply: Feb 24, 2017 3:28 AM by Deepanshu Chamoli

Cumulative

Hello All

I am calculating the running total in visualization based on the months.

When I click on any particular month, the running total is failed.

Is there any way to fix the running total??

I tried to use {1} value to make them static but the issue with that I am not able to filter that value via different filters

Thanks!!

New to Qlik Sense

• Re: Cumulative

Thanks Andrey for the help.

I have already used the formula you mentioned in the link but the issue is that I can not able to drill down any particular month. The running total fails

Regards

DC

• Re: Cumulative

On sheet of Qlik Sense create a new line chart or bar chart. Add Year and Month as the dimensions (or Month-Year - that is in your data). As a measure, use the expression

RANGESUM(ABOVE(TOTAL Sum(Sales),0, ROWNO(TOTAL))),

where  Sales - field field with cumulative sum.

An example of the attached file.

The result, as in the example variant visualization

Regards,

Andrey

• Re: Cumulative

I follow the above steps. I need the data only Monthly basis.

1st Snap:- Using the above formula, we can able to calculate the running total month on month basis.

2nd Snap:- When I select the particular month, the running total fails and it shows only that month data.

Is there any way to resolve the data?

• Re: Cumulative

Try this:

RangeSum(Above(Sum({<MonthYearField>} Sales), 0, RowNo())) * Avg(1)

• Re: Cumulative

Hello Sunny

I am using the below formula for running total. The dimension is Date which I convert in to Month using DATE(Active_date, 'YYYY-MM') formula

Running Total= RANGESUM(ABOVE(TOTAL COUNT(DISTINCT CLINICID), 0, ROWNO(TOTAL))).

But whenever I click on any particular month I shows the error. In the appended chain, I have attached my snaps.

I try to use the formula

RANGESUM(ABOVE(TOTAL COUNT({1}DISTINCT CLINICID), 0, ROWNO(TOTAL))).

The issue with that formula I am not able to filter out the Country & Region.

Please let me know if it makes sense.

• Re: Cumulative

Try like this:

RANGESUM(ABOVE(TOTAL COUNT({1}DISTINCT CLINICID), 0, ROWNO(TOTAL))) * Avg(1)

or

RANGESUM(ABOVE(TOTAL COUNT({<Active_date>}DISTINCT CLINICID), 0, ROWNO(TOTAL))) * Avg(1)

• Re: Cumulative

Sir I have tried all the above formula.

RANGESUM(ABOVE(TOTAL COUNT({1}DISTINCT CLINICID), 0, ROWNO(TOTAL))) * Avg(1)

The issue with that formula is that it makes the table static. I want to filter the data via Country, Region & Market further.

But it does not move at all.

RANGESUM(ABOVE(TOTAL COUNT({<Active_date>}DISTINCT CLINICID), 0, ROWNO(TOTAL))) * Avg(1)

This gives me the same error while filter a particular month. It shows the running total of the particular month only

Just a background.. I have created a table whose dimension is Active Month ( Converting Active date to month)  and attribute are Actual ID & Running total of Actual ID.

I want to filter the data via filter pane i.e. Country, Region & Market.

The main issue is selecting the particular month which can drill down on Country, Region & Market.

• Re: Cumulative

I think it should do what you are expecting. Can you share a sample where this isn't working?

• Re: Cumulative

Sir, I have the excel template for the data.

I want the running total on the basis of ACCID and can be filter out CIN,DIN & MAN.

I have created a pivot for better understanding.

Thanks!! !

• Re: Cumulative

I meant a qvf sample where you can demonstrate its not working for you. I am extremely sorry, but I won't have time to create a sample from the data provided. If you can create a qvf file, I will be more than happy to look at it

• Re: Cumulative

Sure sir.. Sorry for that..

Please find the attached qvd file and excel file for your reference.

As per the requirement, I want that the Running Total becomes static and it provides the correct number if I filter on filters

Using {1} gives the incorrect numbers

• Re: Cumulative

Working for me...

• Re: Cumulative

Thanks sir for all your help!!

Sir if we are using {1} in the formula, there is a issue in filtering CIN

Pag 1: Shows first table excludes {1} and second table includes {1} and it has no filter

For E.g. Apr 2014 we have RT = 9 both table

Page 2:

Shows first table excludes {1} and second table includes {1} and it has filter CIN

For E.g. Apr 2014 we have RT = 7 in first and RT = 9 in second

Thus with using {1} the data does not changes using Filters

• Re: Cumulative

1) You don't have to call me sir . My name is Sunny

2) I already gave you the option to use this expression

RangeSum(Above(Count({<Active_Month, Active_Date>}DISTINCT AccID), 0, RowNo())) * Avg(1)

• Re: Cumulative

Thanks Sunny for the help!!.

The formula works fine in the table. But if I am using it in the KPI and filter on any month. The KPI does not move at all. Can you please help me in this one also?.

In the below snap if I filter on Aug 2014, the running total is 21 but KPI becomes static (by using the above formula which works fine in table) and it is showing 31

Thanks

DC

• Re: Cumulative

I won't use this RangeSum() expression for KPI. Just use simple set analysis to get the value your are looking to get. Please share QVF (not the QVD) if you need more help

• Re: Cumulative

Please find my attached QVF file.

It would be great for me if you tell me how to get the Running total via month

Thanks a lot Sunny for your time and help!!

• Re: Cumulative

Try this:

Max(Aggr(RangeSum(Above(Count({<Active_Month, Active_Date>}DISTINCT AccID), 0, RowNo())) * Avg(1), Active_Month))

• Re: Cumulative

Thanks a lot for your time Sir. The issue has been resolved

Thanks Again

Regards

Deepanshu