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

Create a straight table with Active_date as dimension and two expressions:

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

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

Make a selection only in Active_month field and see if the numbers are different or same for both the expression

Anonymous
Not applicable
Author

Sir, second one is working fine for me.

The issue for that formula is that if I filter on CL, DI & MR it does not changes at all

sunny_talwar

Try this out:

=RangeSum(Above(TOTAL Sum({1<CL = $::CL, DI = $:DI, MR $::MR>} Cases),0,rowno(TOTAL))) * Avg(1)

Anonymous
Not applicable
Author

Sir, the formula works if I select the CI, MR & DI, but the total it is not matching

Please see attached snapshot

Thanks a lot for all the help!!

sunny_talwar

What is the first expression you have used in the chart from your snapshot?

Anonymous
Not applicable
Author

I have not used any formula for the first one... The second formula is created based on first column

sunny_talwar

Deepanshu I have no idea what might be the issue. Without seeing it, it would be difficult to say anything my friend

Anonymous
Not applicable
Author

Yes sir.. I know,, But you helped me a lot in this without even seeing it. Many thanks for your valuable time in this,, Really appreciate it. I am trying to replicate the same in the dummy data and sent you the qvd to find the issue. Thanks a lot

sunny_talwar

Sent me the QVD?

Anonymous
Not applicable
Author

Sir, please find the attached QVD and QVF file.

I want to filter the data on Month wise , CLM,DIM and MAN

Please suggest sir

Thanks a lot in advance