topic Re: RunningCount in QlikView

RunningCount

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Greeting for the day!.

I am new to qlik and trying to build a basic running count table, I had already implemented this into SQL and now wanted to build this in qlik.

Basically, I have 3 major fields, SystemID, AgenName, Date. based on Date I want to run running count.

For more details, attaching the same set of data with required output.

Re: RunningCount

adamdavi3s — Mon, 28 Nov 2016 15:29:08 GMT

Hi,

Do you wish to do this in the script or the front end?

A few threads:

https://community.qlikview.com/thread/65358

https://community.qlikview.com/thread/168319

Full running total in a pivot table | Qlik Community

Re: RunningCount

Mon, 28 Nov 2016 15:32:36 GMT

I wanted to do this on front end as the calculation should change based on dates filtered.

Re: RunningCount

adamdavi3s — Mon, 28 Nov 2016 15:34:50 GMT

Those threads will definitely give you the right idea then.

It is all about RANGESUM(ABOVE

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Re: RunningCount

Mon, 28 Nov 2016 15:48:25 GMT

Sorry, but not able to understand the functionality of running count or running sum.

Can you please help me. attaching the qvw.

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 15:48:39 GMT

Hi Vikas, if it's in a straight table you can check the 'full accumulation' option in the expressions tab.

Re: RunningCount

Mon, 28 Nov 2016 15:51:21 GMT

can you please help me to understand the functionality of accumulation. this is new to me

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 15:53:12 GMT

If you only want to add an autonumeric, you just need to add an expression with "RowNo(TOTAL)"

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 15:54:07 GMT

Sorry, I think this was caused by my bad english, I misunderstood the requirements.

Re: RunningCount

Mon, 28 Nov 2016 15:59:05 GMT

No, let say i select Start date - 4 October and End date - 15 October, the result should be like below

SystemID	AgentName	running count	count
A	A1	1	4
A	A2	2	4
A	A3	3	4
A	A4	4	4
B	B1	1	4
B	B2	2	4
B	B3	3	4
B	B4	4	4
C	C1	1	1

Re: RunningCount

vinieme12 — Mon, 28 Nov 2016 16:09:32 GMT

See Attached

Expression for Running Count

=SUM( AGGR(RangeSum(Above(Count(SystemID),0,RowNo())),SystemID,Key))

Expression for Total

MAX(ALL <SystemID> AGGR(RangeSum(Above(Count(SystemID),0,RowNo())),SystemID,Key))

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 16:14:45 GMT

Hi vikas, please check attachment, I think now I understood it right.

I repeat the count when there is the same system and agent than above, if you don't want it this way just remove the conditional in the expression.

Re: RunningCount

Mon, 28 Nov 2016 16:17:35 GMT

Hi Vineeth,

unfortunately , this is not working while using date filters. please refer enclosed qvw

Re: RunningCount

vinieme12 — Mon, 28 Nov 2016 16:19:17 GMT

it won't because I've not added that to the set expression!

Re: RunningCount

Mon, 28 Nov 2016 16:20:21 GMT

Hey Ruben,

Thank you for your efforts, first filed (count) is working perfectly fine but Total field is not working as required.

Basically, for total i need count based on column SystemID (e.g count of A)

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 16:26:17 GMT

If you want all the rows to count the total just remove the AgentName part of the 4expression so it will be:

Count(DISTINCT TOTAL {<Date={">=$(=Date(vFdate))<=$(=Date(vEdate))"}>} SystemID)

If you want an autonumber for each system ID you can use:

If(Above(TOTAL SystemID)=SystemID,

Above(TOTAL [TOTAL]),

RangeSum(Above(TOTAL [TOTAL]),1))

The 2nd 'TOTAL' is the name of the column

Re: RunningCount

vinieme12 — Mon, 28 Nov 2016 16:36:12 GMT

See Attached updated version

Re: RunningCount

Mon, 28 Nov 2016 16:38:41 GMT

Hi Ruben

Sorry, still the result is not correct, result should be link below (last column)

SystemID	AgentName	Date	Count	TOTAL	result required for Total Should be count of SYstemID
			1	2
A	A1	10/5/2016	1	2	4
A	A2	10/6/2016	2	2	4
A	A3	10/7/2016	3	2	4
A	A4	10/4/2016	4	2	4
B	B1	10/8/2016	1	2	6
B	B1	10/12/2016	1	2	6
B	B2	10/9/2016	2	2	6
B	B2	10/13/2016	2	2	6
B	B3	10/10/2016	3	2	6
B	B4	10/11/2016	4	2	6

Re: RunningCount

rubenmarin — Mon, 28 Nov 2016 16:42:53 GMT

Hi Vikas, that expression can be:

Count(Distinct TOTAL <SystemID> AgentName)

Re: RunningCount

Mon, 28 Nov 2016 16:43:08 GMT

Thank you so much Vineeth, .

One more request, please guide me and explain me, if you have 2minutes