Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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.
Hi Vineeth,
unfortunately , this is not working while using date filters. please refer enclosed qvw
it won't because I've not added that to the set expression!
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)
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
See Attached updated version
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 |
Hi Vikas, that expression can be:
Count(Distinct TOTAL <SystemID> AgentName)
Thank you so much Vineeth, .
One more request, please guide me and explain me, if you have 2minutes