How to build a cross table in QV

[#1]

Hello,

I've already import the table into QV as below:

ApplicantDetails.xlsx

ApplicationID, CardType, ApplicationStatus, CustomerID, CustomerStatus, SnapshotDate

However, I'm not sure how to display SnapshotDate and CustomerStatus (Existing, New) in a Crosstable, whereas ApplicationStatus = Booked.

Like this:-

#Booked	jan-11	feb-11	mar-11
New To Bank	6 00	6 00	6 00
Existing	4 00	4 00	4 00
Total	10 00	10 00	10 00

I tried to use pivot table, but cannot find how to show table looking like as above.

Anybody has some hints for me? Will be much appreciate.

Thank you....

[#2]

If you are able to create a straight table then creating the same in pivot table is easy.

Convert chart type to pivot table. Go to presentation check allow pivoting. Drag the month column on to values.

Hope this helps,

Kiran.

[#3 · Accepted solution]

thanks for your reply. I did what you have told already.

All the values start from the second row downwards.

By the way, what I really need to be shown is that the value for snapshot date starts on the first row..

Also, I'd like to put where condition 'ApplicationStatus = Booked' in the pivot table without fixing this 'Booked' valued on other listbox data..

[stevedark]

Hi there - could you possibly upload an example of where you are presently at - I'm not sure what you mean by 'start from the second row'?

If you have followed Kiran's instructions you should have a pivot table.  This will be obivous from the + and - symbols.  Under the Properties, on the Presentation tab you can click Always Fully Expanded.  This will remove the expand and contract symbols, and you should see each dimension with many rows for the dates (or many dates with many rows for the dimension).  From here you can click and hold the labels and pull the dimensions around to place them where you want them.  This can sometimes be fiddly, but you should be able to get to what you want.  Once you have everything in the right place go back to the Presentation tab and deselect Allow Pivoting.  (If you are not able to drag the dimensions around it may be that Allow Pivoting is already off).

To only include Booked your expression should use Set Analysis and should look like this:

=count({<ApplicationStatus={'Booked'}>}ApplicationID)

To get the totals go back into the Properties and the Presentation tab and select each dimension (Month, Status) and tick Show Partial Sums.  Also on that tab you can select if the totals are at the top or the bottom of the list.

Hope that helps.

- Steve

[#5]

Thank you very much..

What I mean by that is to get a crosstable which is looking like

#Booked	jan-11	feb-11
New To Bank	6 00	6 00
Existing	4 00	4 00
Total	10 00	10 00

By the way, what I've got now is:-

CustomerStatus	SnapshotDate	Count ({<ApplicationStatus={'Existing'}>}CustomerStatus)
Existing	31-Jan-11	400
	31-Feb-11	400
New	31-Jan-11	600
	31-Feb-11	600
Total		2000

Thanks for your helpful answer. I understand that the Pivot table allows me to hold and drop these value anywhere I want. However, what I need is to move the value for SnapshotDate to the Header Row (that's what cross table look like).. I don't know how to do that.

But now it's working fine with fixed where clause statement

=count({<ApplicationStatus={'Booked'}>}ApplicationID)

[stevedark]

That is what I was trying to describe; left click and hold on the SnapshotDate legend, you should then be able to drag this to above the values.  If you don't see a blue line (showing where the dimension will be dropped) you will need to go into the properties and check the Allow Pivoting check box (as described above).

Good luck!

Steve

[#7]

thanks a lot for your help! Anyway, I still have a few question about this expression:-

=count({<ApplicantStatus={'Booked'}>}ApplicationID)

What if I'd like to add one more fixed ApplicantStatus, i.e. 'Rejected' ?

Where should I put OR or AND expression?....

[stevedark]

Hi there, it is just a comma separated list in the Set Analysis:

=count({<ApplicantStatus={'Booked','Rejected'}>}ApplicationID)

- Steve