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
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
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
Try this out:
=RangeSum(Above(TOTAL Sum({1<CL = $::CL, DI = $:DI, MR $::MR>} Cases),0,rowno(TOTAL))) * Avg(1)
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!!
What is the first expression you have used in the chart from your snapshot?
I have not used any formula for the first one... The second formula is created based on first column
Deepanshu I have no idea what might be the issue. Without seeing it, it would be difficult to say anything my friend
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
Sent me the QVD?
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