10 Replies Latest reply on Oct 3, 2013 4:14 PM by Luigi Cicciari

# Sorting on Calculated Fields

Hello Everyone,

I am building a workbook that mashes two excel workbooks together. The first has a list of buyers names and the stores they are assigned to. The other is the amount of spending for each store and the amount of leads generated. I am running into issues with using sorting within the view.

In order to display null values as "0" I utilized ZN formulas (e.g. ZN(lookup(sum([Leads]),0))).

What can I do to allow the calculated fileds to be sorted? In this case Spending, Leads, & Cost Per Lead?

Luigi

• ###### 1. Re: Sorting on Calculated Fields

Hello Again,

Just following up to see if anyone can help me out here. I know there are difficulties sorting when multiple data sources are involved. Any suggestions would be appreciated.

thank you,

Luigi

• ###### 2. Re: Sorting on Calculated Fields

Luigi, Dimensions can be sorted based on Measures, but Measures themselves can't be sorted. I've opened your workbook and here's what I get:

The first four columns are your dimensions, the last three your measures. What do you want the sort to look like?

--Shawn

• ###### 3. Re: Sorting on Calculated Fields

Hi Shawn,

Thank you for your reply. The goal is to be able to click on the measure of the users choice and sort. For example, on the image below, I am able to sort by Leads by clicking on the header row.

I need to find a way to accomplish this when I have two or more data sources.

Luigi

• ###### 4. Re: Sorting on Calculated Fields

Can this be accomplished by joining the pieces when connecting to the data? When I tried this using an INNER join, I was able to sort. However, the subtotals for Leads and Spending where incorrect.

• ###### 5. Re: Re: Sorting on Calculated Fields

Hello,

I found an link that discusses the subtotals & grand totals issuse I am running into when I try to join the two pieces together. However, I am having trouble getting the formulas to work.

http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/

I attached the sample workbook with the data joined.

thank you,

Luigi

• ###### 6. Re: Sorting on Calculated Fields

Hello Again,

I'm still looking for hlep on the data sorting issue. I tried using a LEFT join this time and had some success. When I add store code II withOUT store name II, I am to sort the measure fields and get the correct subtotals. . When I add store name II into the mix, the columns will not sort.

I am almost there. Any help would be appreciated.

Thanks!

Luigi

• ###### 7. Re: Sorting on Calculated Fields

Hello,

Just trying one more time to see if anyone can help here.

thanks,

Luigi

• ###### 8. Re: Sorting on Calculated Fields

Luigi, if you've got the Join worked out, you're now just asking a sort question, right? Try combining the two fields, then putting this on the row shelf, sorting on it and then unchecking the "Show Header" for the combined field. If you post the latest version of your workbook, I can show you how to do this. Thanks,

-Shawn

1 of 1 people found this helpful
• ###### 9. Re: Re: Sorting on Calculated Fields

Hi Shawn,

Attached is the current sample I am working with.

The goal is to let users have the ability to click and sort by Market, Spending, Leads, and Cost Per Lead.

Luigi

• ###### 10. Re: Sorting on Calculated Fields

Hello,

By combining Store Code and Store Name, I am able to sort by Measures. Now the sorting by dimension will not work (Buyer, Store Code, Store Name, and Market). It seems like I can only have one or the other.

Is there anything else I can do?

thank you,

Luigi