0

Sorting on subset of a measure

I am trying to get the following done.

I have a Payables cube with the dimensions Overdue and Vendor.
And a measure: Remaining Amount LCY
Of course there are more dimensions and measures, but these are used in what I am trying to accomplish :)

When I create a pivot table based on that cube, I can add the measure in the value part of the pivot table, the dimension Overdue in the column part and the vendors in the row part.
So I get 3 columns for each vendor: total not overdue, total overdue and total remaining
In a pivot table you can sort the vendors based on a total column and even on a total of one dimension, meaing I get a list of vendors sorted by the amount overdue instead of the total amount remaining.

So far so good.

Now I want to recreate that pivot table using Jet functions.
My first take on this resulted in this NL function

=NL("Rows";"Payables";"[Buy-from Vendor].[Vendor no]";"+[Measures].[Remaining Amount LCY]";"*")
That generates a list of vendors sorted by the total amount remaining.
But I want them sorted by amount overdue, not the total amount.
So I tried this

=NL("Rows";"Payables";"[Buy-from Vendor].[Vendor no]";"+NL(""CubeValue"";""Payables"";""[Measures].[Remaining Amount LCY]"";""[Overdue]"";""Overdue"";""[Buy-from Vendor].[Vendor no]"";NF(;""[Buy-from Vendor].[Vendor no]""))";"*")
But that is not accepted by Jet. It says the field 'NL("CubeValue";"Payables";"[Measures].[Remaining Amount LCY]";"[Overdue]";"Overdue";"[Buy-from Vendor].[Vendor no]";NF(;"[Buy-from Vendor].[Vendor no]"))' is not valid.

Is there a way to sort on a dimension value total instead of the general total?

rmw

2 comments

Please sign in to leave a comment.