Sort with Match() of calculated dimension field

[#1]

Hello all,

In my script I load the following:

IF([Artikel] >=110000 and [Artikel]<=119999, 'CategoryA',

IF([Artikel] >=130000 and [Artikel]<=149999, 'CategoryB',

IF([Artikel] >=160000 and [Artikel]<=189999, 'CategoryC',

IF([Artikel] >=160000 and [Artikel]<=189999, 'CategoryD',

IF([Artikel] >=160000 and [Artikel]<=189999, 'CategoryE',

IF([Artikel] >=160000 and [Artikel]<=189999, 'CategoryF',

...etc.....

)))))) as Category

In my Pivot Table, I have a calculated dimension 'Category' in which I only want to load a few of these categories with specific names given in the table.

=if(Category='CategoryA', 'CategoryProductA',

if(Category='CategoryB' or Voercategorie='CategoryD', 'CategoryMerged',

if(Category='CategoryF', 'CategoryProductF'

...etc.... ))

(both contain more Categories and completely different names, but just to keep it "simple".)

So for one row, I combine two Categories together, the sum of CategoryB and CategoryD and the field in the dimension is named CategoryMerged

Next I want to sort these categories in a specific order.

I use the Match function in the Sorting-tab, to get this specific order.

For those categories that I didn't merge in my calculated dimension, the sort order works.

But the CategoryMerged (which in the load script are two different categories, I want to have on a specific place also, but that one doesn't work.

So I use the following expression to sort:

=match(Category, 'CategoryF', 'CategoryA', 'CategoryMerged', 'CategoryC')

Is it possible to show the CategoryMerged on a specific place also?

At the moment I can't get it to work.

Thanks in advance.

[jagan]

Hi,

Try with Dual function and select Numeric in Sort tab

IF([Artikel] >=110000 and [Artikel]<=119999, Dual('CategoryA', 1),

IF([Artikel] >=130000 and [Artikel]<=149999, Dual('CategoryB', 2),

IF([Artikel] >=160000 and [Artikel]<=189999, Dual('CategoryC', 3),

IF([Artikel] >=160000 and [Artikel]<=189999, Dual('CategoryD', 4),

IF([Artikel] >=160000 and [Artikel]<=189999, Dual('CategoryE', 5),

IF([Artikel] >=160000 and [Artikel]<=189999, Dual('CategoryF', 6),

...etc.....

))))))

Hope this helps you.

Regards,

Jagan.

[pauljohansson]

Hi,  Cant you just create CategoryMerged in the script and problem sovled?  br Paul

[swuehl · Accepted solution]

Have you tried something like

=if(Category='CategoryA', dual('CategoryProductA',1), ...

to assign sort order together with the string representation and the use a numeric sort in the sort tab for the dimension?

[#5]

No I haven't used this dual.

I could change my load script, but I use this part of load script for many other tables etc where I do need these categories seperated.

I only need to combine these two fields now just for one table.

Perhaps I could add just another part to my script, to merge these two Categories and call it different (Category2), but if I do so, will I be able to match from to different tables in my sorting order?

So could I use

=match(Category, 'CategoryF', 'CategoryA', match('Category2', 'CategoryMerged'), 'CategoryC')

(I don't believe this is goint to work, a match function in a match function)

[swuehl]

My initial suggestion was to use the dual() in your calculated pivot table dimension (though incorporating your classifications as a table in the script might be even better).

Have you tried this?

[#7]

Thank you Swuehl, that indeeds works!

I used the Dual() in my dimension and sorted this dimension on Numeric Value.