topic Re: limit rows in pivot table in QlikView

limit rows in pivot table

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

Hi All,

I want to limit the no. of rows in the pivot table.

Currently you can see there are 9 products. I want to show 4 products in each pivot table, ie. in first table i want to show A to D Product, in 2nd pivot table E to H Products details and in final table the remaining Product ie, J.

Please help

Re: limit rows in pivot table

pablolabbe — Tue, 16 Jan 2018 12:02:55 GMT

You can do this with set analysis .

Example for Table 1

sum({<Product={A,B,C,D}>}Margin)/sum( {<Product={A,B,C,D}>} Sales)

If you are new to set analysis, check to this article https://community.qlik.com/blogs/qlikviewdesignblog/2015/02/16/set-analysis for an introduction.

Regards,

Pablo

Re: limit rows in pivot table

surajap123 — Tue, 16 Jan 2018 12:13:31 GMT

Thanks for your reply Pablo.

I real data has thousand's of products. so i think its not eazy to handle using SA.

Re: limit rows in pivot table

karthikoffi27se — Tue, 16 Jan 2018 12:18:35 GMT

Hi Suraj,

try using If condition in calculated dimension in that way we can limit the data.

Many Thanks

Karthik

Re: limit rows in pivot table

surajap123 — Tue, 16 Jan 2018 12:22:14 GMT

is it possible to write the calculated dimension using a range. ie assigning the products to numbers and those numbers we put in the cal dimension.

Could you please help with with the expression.

Re: limit rows in pivot table

pablolabbe — Tue, 16 Jan 2018 12:28:28 GMT

So you need to define the range scope for each table.

The range scope will be based on product attribute or sales ammount ?

Re: limit rows in pivot table

sunny_talwar — Tue, 16 Jan 2018 12:37:09 GMT

Like the attached

Re: limit rows in pivot table

surajap123 — Tue, 16 Jan 2018 12:38:23 GMT

i want to have range based on product name, as in my example.

could you please help me with the expression(calculated dimension)

Re: limit rows in pivot table

surajap123 — Tue, 16 Jan 2018 12:56:49 GMT

Thank you so much for the expression/solution.

I have a final query.

The below set analayai is to filter out the products with no sales. Am i right?

{<Product = {"=Sum(Margin)/Sum(Sales) > 0"}>}

If yes, then i see all my products has sales i hope i can simplify the expression like below, do you agree?

=Aggr(

If((Count(DISTINCT Total Product) - Rank(Only(Product))+1) <= 4,

Product),

Product)

Re: limit rows in pivot table

sunny_talwar — Tue, 16 Jan 2018 12:59:22 GMT

Yes that is what it was meant to do... because f didn't have any margin or sales and was not showing up on chart 2.... but if everything in your real data have margin and sales... you can remove the set analysis

Re: limit rows in pivot table

pradosh_thakur — Tue, 16 Jan 2018 13:01:37 GMT

what about

in table1

expression1= if(rowno()<=4,your expression)

in table 2

expression1= if(rowno()>4,your expression)