P&L problem with "Sort by"

[AndrejZitnay]

Hello all,

I have problem with my "Sort by" for my P&L order.

Just to give you bit of context : GL00201 is my budget, GL11110 are my actuals, GL00100 is set up of my accounts. ACTINDX Table is unique link for first 3 tables, Profit Centres are working, Sheet 1 is list of my nominal codes. I have here problem.

Here is my Nominal code table. I've tried to use Nominal Code Order. I've tried Index order. Nothing is working.

My P&L order should be type, category & NC full string. Unfortunately I have correct order only for NC full string within category. Category or type is not in right order.

Contracted Fees should be at top.

We are working with expanding hierarchy level as I have my profit centres under nominal codes.

Can you help?

Many thanks.

Andrej

[jdbuchanan71]

Hello @AndrejZitnay 

To confirm, you are setting the sorting by selecting the Subcategory column, then setting the Sort By colmn correct?

[AndrejZitnay]

Hello @jdbuchanan71 

I was trying but it doesn't allow me to sort the "Category" columns by "Index". There can't be more than one value in "Index" for same value in "Category".

I am sorting my NF Fully string by Index.

My order in Matrix table is Type, Category & NC Full String.

I have correct order of NC Full String but only within Category. Not in full P&L

I am bit lost. I've checked several YouTube videos and I always end up in same problem.

I need to always expand one level down from Type, to Category to NC Full String.

[ajayemmadi1]

Hello Mr. AndrejZitnay,

Can you check whether the index column data type is either Whole number format or Text. If it is text format then change it to number format in edit queries and then do sorting category by index. I hope the issue will be resolved.

Regards,

Ajay Emmadi

[AndrejZitnay]

Hello @ajayemmadi1 

It is. Index is whole Number.

Before Index was trying to duplicate my Nominal Codes and change that to number for sort option.

None of these get me right order ofr Type or Category. I always have only corect Order for NC Full String within Category. 

Thanks for trying to help me.

[ajayemmadi1 · Accepted solution]

Hello AndrejZitnay,

I understood the issue now. You need to create a conditional column(TypeOrder) for Type where
ADMINISTRATION COSTS =1,
DIRECT COSTS = 2,
INCOME = 3

Create one more conditional column for Category similary and indicate the order of each value.
Later you can do sorting based on TypeOrder and Type . I hope it works for sure. Let me know again.

Regards
Ajay Emmadi

[AndrejZitnay]

Hello @ajayemmadi1 

You are star. 

All is in good order now.

Many thanks.

Andrej