Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jmcdermott
New Contributor II

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

Account NameAcct#Crd Hold?Risk Codeposted_balCredit AvailCredit LimitAR_Aging_ClassTotal DueAge 151+Age 121-150Age 91-120Age 61-90Age 31-60Age 1-30Age 0Cur
Company A107NLOW           789           6,711           7,500 $789.--$79.-$129.$233.$347.
Company B139NLOW        2,619           7,381         10,000 $2,619.-----$1,368.$1,251.
Company C153NLOW      13,023         46,977         60,000 $13,023.--$488.-$130.$1,747.$10,658.
Company D244NLOW      18,962       131,038       150,000 $18,962.($337.)---($599.)$5,561.$14,336.
Company E251NLOW      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

7 Replies
Arjunarao
Honored Contributor II

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

Can you attach the sample file?

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

jmcdermott
New Contributor II

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

How do I attach file?

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

saratchandra30
New Contributor III

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

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
New Contributor II

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

!Outputfrom pivot chart.gifWhat I want output to be.gif

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
New Contributor II

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

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