Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Regards
Deepanshu
Try this:
=RangeSum(Above(TOTAL Sum({<Active_Date>}Cases),0,rowno(TOTAL))) * Avg(1)
Sir, If I select the particular month, it shows only current Month value not the running total
Please suggest
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)
Replace MonthField with whatever you call your month field to be.
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
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?
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
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?
Yes sir.. But on the same time if I filter on CL, MR and DI the running total changes
Regards
Deepanshu