topic Show incremental count against records in QlikView

Show incremental count against records

TheMdu — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I want to add a column on a pivot /tablewhere every payment received from a client a record is added. For example a client’s account was opened in Jan 2020 and started making payment in Jan. We’ve received 9 payments so far. I want the column to show all payments with increment of 1, across the entire client database. I had a look at rec() and row() an they seem to be the closest functions to what I’m trying to achieve, but counter should start at 1 for every client

Re: Show incremental count against records

rushikale0106 — Thu, 10 Sep 2020 10:29:29 GMT

Hi,

I don't know how much it will help you to solve queries. But I guess you can check following links for any guidance.

https://community.qlik.com/t5/New-to-QlikView/What-is-NoOfColumn-and-Column-No/td-p/859720

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/InterRecordFunctions/columnno.htm#:~:text=ColumnNo()%20returns%20the%20number,first%20column%20is%20number%201.&text=If%20the%20table%20is%20one,equal%20to%20the%20entire%20column.

Let me know if it is helping.

Regards,

Rushi

Re: Show incremental count against records

Kushal_Chawda — Thu, 10 Sep 2020 12:38:20 GMT

@TheMdu Can you share sample data with expected output?

Re: Show incremental count against records

edwin — Thu, 10 Sep 2020 13:32:33 GMT

try this as expression in straight table:

=if(isnull(above(Client)),1, //this is for the first record
if(above(Client)=Client,Above([Counter])+1, //prior client is the same so increment
1) // new client
)

assuming Client is the field name for client, there must be some PayDate (this is what you sort to order your payments)

call your counter expression as Counter (you increment this by 1 if prior client is the same as current client, else set to 1 (if first payment)

you need to determine the counter dynamically and not in script, once filters are applied and you used rowno() as counter in your script, then some of the counters will not be selected thus you will have missing counter records