topic Re: Row count in pivot table in QlikView

Row count in pivot table

Thu, 23 May 2013 04:38:19 GMT

Hi,

I have a pivot table, it contains Day wise calls closed by the analyst. I want to count if the analyst closed a call on a day, then the count should be 1

and i want a row count for the 31 days showing the no of calls closed .I dont want the sum of the rows. I want the count of rows.

For further Clarity I have attached my excel sheet.

For Eg: Analyst A , the total shows as 22 in the end, how to do this in the qlikview. In Excel we can use a count A function to retrieve the count of rows how is it possible in qlikview.

Awaiting for reply at the earliest....

Re: Row count in pivot table

Thu, 23 May 2013 05:49:21 GMT

load your table as below,

CrossTable(Resolved_Day_new,value,2)

LOAD Analyst,

Resolved_Day,

[1],

[2],

[3],

[4],

[5],

[6],

[7],

[8],

[9],

[10],

[11],

[12],

[13],

[14],

[15],

[16],

[17],

[18],

[19],

[20],

[21],

[22],

[23],

[24],

[25],

[26],

[27],

[28],

[29],

[30],

[31]

FROM

(biff, embedded labels, table is Sheet1$);

then take a pivote chart

dimension-- Analyst

Resolved_Day_new

Expression--

sum(value)

count(if(value='-',Resolved_Day_new))

count(if(value>0,Resolved_Day_new))

then go to prperties of pivote--> presentation tab--> select field Resolved_Day_new --> check on partial sum

then output like this

see attachement

Re: Row count in pivot table

Thu, 23 May 2013 06:44:13 GMT

Hi ,

Thanks for the reply,the solution did not work as i am having one field called closed Date, I am deriving resolved day from the close Date using the makedate formula. I dont have a separate field called resolvedDay.

Regards,

B.Vijayalakshmi

Re: Row count in pivot table

Mon, 27 May 2013 10:25:42 GMT

Hi,

Could you please help me in creating the above cross table. If you could share me the steps as how to create this cross table

Re: Row count in pivot table

Mon, 27 May 2013 10:40:19 GMT

syntax for creating crosstable.

CrossTable(Resolved_Day_new,value,2)

load *

from table;

here 2 represent the second field of your table that is qualifier 2, it means after that field all fields converted into single field and its name as Resolved_Day_new, and its value keep in the second field as value.

*******************************************

Crosstable

A cross table is a common type of table featuring a matrix of values between two orthogonal lists of header data. To turn a cross table into a straight table, use a crosstable prefix.

The syntax is:

crosstable (attribute field , data field [ , n ] ) ( loadstatement | selectstatement )

where:

attribute field is the field to contain the attribute values.

data field is the field to contain the data values.

n is the number of qualifier fields preceding the table to be transformed to generic form. Default is 1.

Examples:

Crosstable (Month, Sales) Load * from ex1.csv;

Crosstable (Month,Sales,2) Load * from ex2.csv;

Crosstable (A,B) Select * from table3;

Crosstable Wizard

The crosstable wizard is dialog driven method of creating the crosstable syntax. This dialog is opened by clicking the Crosstable button in the Options page of the File Wizard. The crosstable wizard holds the following options:

Qualifier Fields	The number of qualifier fields that precede the fields to be transformed.
Attribute Field	The name of the new field that will contain all the fields (attribute values) to be transformed.
Data Field	The name of the new field that will contain the data of the attribute values.