Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
vipingarg23
Creator
Creator

Count number of rows in straight table

Hi Team,

How to count number of rows in straight table? Please find the attached sample

In this sample I want total number of rows in text box Means Here, I have 3 rows, So I want 3 as a result in text box or in Caption?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

There is is. unfortunately, no quick and simple way to do this outside of the table itself.

You can get close: Create a list of he field names that make up the possible dimensions in the report, and then use selection from this field when designing the report.

  • Create a variable (vAttRFields) with a leading =:
    • Set vAttrFields = =If(GetSelectedCount(ReportAttribute), Concat(ReportAttribute, ','), 1), ','), 1); 
    • Where ReportAttribute is the field that contains the list of possible dimensions
  • Then use the expression
    • =Sum(Aggr(1, $(vAttrFields)))

This can be moderately expensive to calculate if the data set is large and you have many selected dimensions. This will NOT take into account the effect of suppressing zeroes, which reduces the row count. It will also not work with calculated dimensions.

You could modify this second expression to do a more accurate row count, but it would be effectively be calculating the table again, in memory this time:

    =Sum(Aggr(Alt(If(<Your expression here>) <> 0, 1, 1), 0),  $(vAttrFields))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
stabben23
Partner - Master
Partner - Master

You could use Count, in straight table you need to use Count(Total A) to achive 3 on all rows.

In textbox you could use just Count(A) or Count(Total A).

vipingarg23
Creator
Creator
Author

Sorry, I forgot to mention, that I am using adhoc report where I have lots of dimensions and user can select any dimension as per choice, So count won't work here..

petter
Partner - Champion III
Partner - Champion III

The function NoOfRows(TOTAL) can be used within the straight table.

jonathandienst
Partner - Champion III
Partner - Champion III

There is is. unfortunately, no quick and simple way to do this outside of the table itself.

You can get close: Create a list of he field names that make up the possible dimensions in the report, and then use selection from this field when designing the report.

  • Create a variable (vAttRFields) with a leading =:
    • Set vAttrFields = =If(GetSelectedCount(ReportAttribute), Concat(ReportAttribute, ','), 1), ','), 1); 
    • Where ReportAttribute is the field that contains the list of possible dimensions
  • Then use the expression
    • =Sum(Aggr(1, $(vAttrFields)))

This can be moderately expensive to calculate if the data set is large and you have many selected dimensions. This will NOT take into account the effect of suppressing zeroes, which reduces the row count. It will also not work with calculated dimensions.

You could modify this second expression to do a more accurate row count, but it would be effectively be calculating the table again, in memory this time:

    =Sum(Aggr(Alt(If(<Your expression here>) <> 0, 1, 1), 0),  $(vAttrFields))

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vipingarg23
Creator
Creator
Author

Great!!! It is working fine. Thanks for your help.

vipingarg23
Creator
Creator
Author

No, it is not working.

dapostolopoylos
Creator III
Creator III

That was exactly, what i needed!

Father/Husband/BI Developer