Sort Z-A in a partial sum field and exclude one value from a formula

[jmcdermott]

Account Name	Acct#	Crd Hold?	Risk Code	posted_bal	Credit Avail	Credit Limit	AR_Aging_Class	Total Due	Age 151+	Age 121-150	Age 91-120	Age 61-90	Age 31-60	Age 1-30	Age 0Cur
Company A	107	N	LOW	789	6,711	7,500		$789.	-	-	$79.	-	$129.	$233.	$347.
Company B	139	N	LOW	2,619	7,381	10,000		$2,619.	-	-	-	-	-	$1,368.	$1,251.
Company C	153	N	LOW	13,023	46,977	60,000		$13,023.	-	-	$488.	-	$130.	$1,747.	$10,658.
Company D	244	N	LOW	18,962	131,038	150,000		$18,962.	($337.)	-	-	-	($599.)	$5,561.	$14,336.
Company E	251	N	LOW	19,525	10,475	30,000		$19,525.	$1,055.	-	-	$92.	$1,278.	($657.)	$17,756.
2 Questions -
1. this is a output from a pivot chart I have.  I am wanting the Total Due Field to show in Desending order, in dimension 'AR_Aging_Class' I have Show Partial Sums selected and 'Total Due' as the Label for Totals.  I can't find where to make it sort in decending order?
2. I want a Column that is Past Due…Which would be 'Total Due' minus 'Age0Cur'?

Thanks,

Josh

[qlikviewwizard]

Can you attach the sample file?

[marcus_sommer · Accepted solution]

Take a look here: Perfectly Sorting Pivot Table (by A-Z. y-Value set for each dim-level)

- Marcus

[jmcdermott]

How do I attach file?

[marcus_sommer]

See here:

Uploading a Sample

Preparing examples for Upload - Reduction and Data Scrambling

- Marcus

[Anonymous]

Hi Josh,

U can attach a file by going to Use advance editor option on the right corner --> then Attach on the bottom right on your next screen.

1. And coming to your question in a pivot table you can sort and do a sum only based on a Dimension and not based on any expression so if you need to sort and sum based on any other column Straight table is a better option. Please find the sample Tutorial Final Qlikview document. Refer Geography sheet --> Pivot and Straight table. Also, refer below link for the differences.

https://community.qlikview.com/thread/80815

2. You can do this by adding a new expression and in the expression using the names given as the expression names. Please see attached for the Difference field in which I have subtracted the earlier expressions.

Hope I have helped answer your question. If not please give a sample Qlikview document.

Thanks,

Sarat.K

[jmcdermott]

!

This is all I could figure out how to attach "Use advance editor option on the right corner" I can't find on my screen.   I tried a straight table, and I definitely need to be using the pivot.  I got it to sort by adding "posted bal" as the first column, but I want that dimension hidden, can I do that?

[jmcdermott]

I uploaded excel file under New to Qliksense, and

Sort Z-A in a partial sum field and exclude one value from a formula

is the title.  The file is called Sample File.xls