topic Re: Running Total with filters in App Development

Running Total with filters

Anonymous — Tue, 07 Jun 2016 12:09:28 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 12:11:34 GMT

Try this:

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

Re: Running Total with filters

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

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

Please suggest

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 12:22:12 GMT

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)

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 12:22:58 GMT

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

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 13:21:18 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 13:29:38 GMT

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?

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 14:14:22 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 14:22:32 GMT

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?

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 14:26:42 GMT

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

Regards

Deepanshu

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 14:32:46 GMT

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

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 16:17:15 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 16:19:17 GMT

Try this out:

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

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 17:13:16 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 17:15:00 GMT

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

Re: Running Total with filters

Anonymous — Tue, 07 Jun 2016 17:17:56 GMT

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

Re: Running Total with filters

sunny_talwar — Tue, 07 Jun 2016 17:33:51 GMT

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

Re: Running Total with filters

Anonymous — Fri, 10 Jun 2016 10:02:23 GMT

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

Re: Running Total with filters

sunny_talwar — Fri, 10 Jun 2016 10:04:23 GMT

Sent me the QVD?

Re: Running Total with filters

Anonymous — Sat, 11 Jun 2016 06:38:41 GMT

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