Row count in pivot table

[#1]

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....

[#2]

hi

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

[#3]

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

[#4]

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

[#5]

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.