Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

 

23 Replies
sunny_talwar

Try this:

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

Anonymous
Not applicable
Author

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

Please suggest

sunny_talwar

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)

sunny_talwar

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

Anonymous
Not applicable
Author

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

sunny_talwar

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?

Anonymous
Not applicable
Author

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

sunny_talwar

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?

Anonymous
Not applicable
Author

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

Regards

Deepanshu