Table Format

[User12321]

Hi,

I want to create a table with rows (no. of suppliers, avg no. of suppliers etc) and columns (current, previous year etc).

Please advise on how I can go about creating this table. Thank you!

[abhijitnalekar]

Hi @User12321,

The required out will not achievable in a straight or pivot table. but you can create the same structure with the help of KPI and Text objects and place it like a table. 

[AshutoshBhumkar · Accepted solution]

Hi, You can try like below.

Create an Inline and give order to each and every line:

Load * Inline [

KPI, SrNo

No of Suppliers,1

Avg no of supp,2

];

Now in straight table, you can put KPI as dimension and Measure 1 (Total Number) and use PICK() to calculate each cell in table like below

Pick(  SrNo,

Count(Suppliers),

Sum(Avg no of suppliers)   )

For Measure 2 (vs Prev Year)

Pick(  SrNo,

Count({<Prev Year>}Suppliers),

Sum({<Prev Year>}Avg no of suppliers)   )

and same for Measure 3.

Screenshot for reference :

[User12321]

For row avg no. of suppliers, it's showing - in the table. 

Not sure what I'm doing wrong. Please help.

[AshutoshBhumkar]

Hello,

That should work. Must be some bracketing issue.

Try putting the 1st measure in the second just to check if that appears or not.

E.g

Pick(  SrNo,

Count(Distinct Supplier),

Count(Distinct Supplier)  )

[User12321]

Hello,

Tried bracketing it but still not working.

Any idea what else I can try to solve this problem?

[mikaelsc]

try using valuelist()... 

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Sy...