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 with filters

Hello All

Please help in the below query of Qlik Sense,

I have the six columns Active Date, CL, DI, MR, ACID and MR.

 

I have calculated the Running total based on the ACID in the straight table on monthly basis in Qlik visualization as

 

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

 

But the issue is that when I use the filters as CI, DI and MR, the Running total is not changed at all.

 

Can it is possible to change the formula so that it changes as per the filters?

 

Also, I want a KPI which calculate the % as (Cases for particular month)/ Running total of particular month.

 

I am not able to calculate the above using the below formula:

 

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

 

Attached is the file for your reference.

Please suggest & help

stalwar1

Regards

Deepanshu

 

Tags (1)
23 Replies
MVP
MVP

Re: Running Total with filters

Try this:

=RangeSum(Above(TOTAL Sum({<Active_Date>}Cases),0,rowno(TOTAL))) * Avg(1)

deepanshu_chamo1
New Contributor

Re: Running Total with filters

Sir, If I select the particular month, it shows only current Month value not the running total

Please suggest

MVP
MVP

Re: Running Total with filters

Your database you have shared doesn't have any month field, are you creating it in the script? If you are then do this:


=RangeSum(Above(TOTAL Sum({<Active_Date, MonthField>} Cases),0,rowno(TOTAL))) * Avg(1)

MVP
MVP

Re: Running Total with filters

Replace MonthField with whatever you call your month field to be. 

deepanshu_chamo1
New Contributor

Re: Running Total with filters

Yes sir.. The ACID data comes from one table which do not have all the months, however Cases are coming in different table.

I have created a master calendar for Account Table date to normalize it and named as Active_month.

When I used it in the above formula, I am still facing the same issue :-(

Thanks sir for helping me a lot

MVP
MVP

Re: Running Total with filters

So this isn't working?

=RangeSum(Above(TOTAL Sum({<Active_Date, Active_month>} Cases),0,rowno(TOTAL))) * Avg(1)


Are you making selection in any other date related field here? Year or MonthYear or something else? May be share some screenshots to show the issue?

deepanshu_chamo1
New Contributor

Re: Running Total with filters

Yes sir.. It is not working.

Sorry I am not able to give the screenshots as it was in my Client Machine

I just use the Active Date.. In Account Table we have different date Column with few dates missing...

Thus I created a Master Calendar to get those dates and named the column as Active_Date.

In Case Table I have all the dates but to join both the tables I use the Alias as Active Date

MVP
MVP

Re: Running Total with filters

So lets take a step back. When you select a particular month, you are seeing a non-cumulative value for that particular month? whereas you would want to see cumulative values regardless of what month has been selected?

deepanshu_chamo1
New Contributor

Re: Running Total with filters

Yes sir.. But on the same time if I filter on CL, MR and DI the running total changes

Regards

Deepanshu