Solved: Re: Limitation of Rows in simple Table - Qlik Community

canerkan · ‎2019-04-17

Hi everyone,

I am looking for a way to limit the number of rows in a simple Table. Does anyone know how to do that?

Currently I am using an input field extension with a variable, where the user puts in the number of Top Customers he wants to see and the table limits the Dimensions (fixed number in Dimension). The problem is, that there is no way to choose which column should be used to limit the data. So if I have a table like:

Customer | Sales 2018 | Sales 2017 |Sales 2016

The Top Customers are always selected by the Sales of 2018 on default, because it is the first column with values. Therefore the user wont be able to select the Top Customers of 2017 or 2016.

So I thought if I could just limit the number of Rows with my input field, the user could sort the table by the year he wants to see the top customers of.

I hope I was able give you an idea of where I want to go with that

It would be great if someone could help to get this done! Or maybe somebody knows a better/easier way to get there. Every help is appreciatet!

HirisH_V7 · ‎2019-04-18

Check out attached using,

Expression like this:

If(GetSelectedCount(Date)>=1,
If(Rank(Sum({$<Date={"*2018"}>}Sales))<=$(TopCustomer),Sum({$<Date={"*2018"}>}Sales)),
Sum({$<Date={"*2018"}>}Sales))

Show Column:

Sum(Total {$<Date={"*2018"}>}Sales)>0

PFA for Ref.

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

miskinmaz · ‎2019-04-17

Give another input field for sorting the table.
Use this variable in the sorting expression of the table like : sum($(vVar))

HirisH_V7 · ‎2019-04-17

Hi ,
Just to understand, if we limit rows to let say 10, means it will show only 10 customers in dimension. Even if u pass top 11 or greater into your variable, it will not show up.

Also the customer names will vary per year so it will be dynamic.

"So I thought if I could just limit the number of Rows with my input field, the user could sort the table by the year he wants to see the top customers of." Could you please explain this clear, especially that "user could sort" what is this.

HirisH
“Aspire to Inspire before we Expire!”

HirisH_V7 · ‎2019-04-17

For limiting rows, you can pass something like this in expression. Anyway you can pass the number via variable.

If(RowNo()<=10,Sum(Sales))

HirisH
“Aspire to Inspire before we Expire!”

canerkan · ‎2019-04-17

Thanks for your reply Miskinmaz!

unfortunately that doesn't solve my problem that you dont have any influence on what column is being used to limit the data... except rearranging the columns everytime...

canerkan · ‎2019-04-17

Hi HirishvZ,

thanks for your reply.

@HirisH_V7 wrote:
Hi ,
Just to understand, if we limit rows to let say 10, means it will show only 10 customers in dimension. Even if u pass top 11 or greater into your variable, it will not show up.

If you put in 20 in the input field it will show top 20 customers in dimensions, if that was your question. The amount of data will alway adapt to the value you pass into the variable, except there is not enough..

You can only insert numbers. If there is text or an operator it wont show anything.

You can find a picture of the way I am using the variable to limit the dimension attached. "Feste Spaltenzahl" means "fixed number" and "Oben" means "Top"

@HirisH_V7 wrote:
Also the customer names will vary per year so it will be dynamic.

Yes, exactly.

@HirisH_V7 wrote:
"So I thought if I could just limit the number of Rows with my input field, the user could sort the table by the year he wants to see the top customers of." Could you please explain this clear, especially that "user could sort" what is this.

Since it is a simple table, the user just needs to click on a column to sort the whole table by its values . My idea was to limit the possible rows that can be filled with data and the user decides what data is being filled in through the way he sorts the table. So Im not trying to limit the dimensions anymore, I am trying to limit the Table Rows, if that makes sense.

I hope i was able to clearify my intentions.. If you have a better Idea I would be happy to hear about it. Im afraid that my plan wont work out...

I tried your formula but unfortunately it showd the wrong values. It looks like they were the bottom values instead of top values..

miskinmaz · ‎2019-04-18

Hi Canerkan,

Please put up a sample app if possible.

canerkan · ‎2019-04-18

Hi Miskinmaz,

You can find my sample .qvf attached. If you focus on the Top 5 you will see, that there are different Top Customers each year. If you limit the dimension on Top 5, you will see that the Top Customers for 2017 and 2016 are not right.

HirisH_V7 · ‎2019-04-18

was these rows depends on Top and bottom, i mean Rank of customers w.r.to Sales. If so, we can do using rank function in expression.

Also, can u pl make a sample app and plot the requirement clearly. So that we will on same page to try and achieve required.

HirisH
“Aspire to Inspire before we Expire!”

canerkan · ‎2019-04-18

Hi Hirishv7,

you are right, the customers are ranked w.r. to sales.

I've added the requirements to the sample.qvf. You can find it attached. I hope it is clear enough.

Thank for helping me, Hirishv7 and Miskinmaz!

Limitation of Rows in simple Table

Data limitation

Row limitation

table limitation