RunningCount

[#1]

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.

[vinieme12]

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

[rubenmarin]

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.

[#4]

Hi Vineeth,

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

[vinieme12]

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

[#6]

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)

[rubenmarin]

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

[vinieme12 · Accepted solution]

See Attached updated version

[#9]

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

[rubenmarin]

Hi Vikas, that expression can be:

Count(Distinct TOTAL <SystemID> AgentName)

[#11]

Thank you so much Vineeth, .

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