Conditional dimension ranking in pivot table

[Vince_CH]

 Dear Community,

I have one pivot table, with conditional dimensions with expressions as following. with final looking like picutre showed, actually what I expect is to rank the dimension sequence like 'Ground, Sea, Air, Express, Rest'. 

I have tried to rank as per expressions, but it didn't work out.  Could some expert help with this? thanks!

=If([Forwarder 货代]='赋友' or [Forwarder 货代]='乾运' or [Forwarder 货代]='广悦','Ground',

If(([Forwarder 货代]='泓明' or [Forwarder 货代]='伊森' or [Forwarder 货代]='OTIM' or [Forwarder 货代]='HMG' or [Forwarder 货代]='EASON') and
([运输方式]='LCL' or [运输方式]='FCL'),'Sea',

If(([Forwarder 货代]='泓明' or [Forwarder 货代]='伊森' or [Forwarder 货代]='HMG' or [Forwarder 货代]='EASON'
or [Forwarder 货代]='Logwin' or [Forwarder 货代]='LOGWIN') and ([运输方式]='by air'),'Air',

If([Forwarder 货代]='DHL','Express',

If([Forwarder 货代]<>'赋友' and [Forwarder 货代]<>'乾运' and [Forwarder 货代]<>'广悦' and [Forwarder 货代]<>'泓明'
and [Forwarder 货代]<>'伊森' and [Forwarder 货代]<>'OTIM' and [Forwarder 货代]<>'HMG' and [Forwarder 货代]<>'EASON'
and [Forwarder 货代]<>'Logwin' and [Forwarder 货代]<>'LOGWIN' and [Forwarder 货代]<>'DHL','Rest')))))

[OmarBenSalem]

In the sorting tab;

Sort ur dim by expression (numeric asc): try the same thing u did to create ur dimension but this time attribuite numeric values.

instead of:

if(conditon1,'Ground',..)

if(condition1,1... etc

[Vince_CH]

@OmarBenSalem, thanks.
But how to attribute numeric values to in the conditional dimension expressions?
If you have time can you help to indicate in details?

[kaanerisen · Accepted solution]

Hi Vince,

Edit your expression for giving the numeric representation of calculated field.

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/Formatting...

=If([Forwarder 货代]='赋友' or [Forwarder 货代]='乾运' or [Forwarder 货代]='广悦',DUAL('Ground',1),

If(([Forwarder 货代]='泓明' or [Forwarder 货代]='伊森' or [Forwarder 货代]='OTIM' or [Forwarder 货代]='HMG' or [Forwarder 货代]='EASON') and
([运输方式]='LCL' or [运输方式]='FCL'),DUAL('Sea',2),

If(([Forwarder 货代]='泓明' or [Forwarder 货代]='伊森' or [Forwarder 货代]='HMG' or [Forwarder 货代]='EASON'
or [Forwarder 货代]='Logwin' or [Forwarder 货代]='LOGWIN') and ([运输方式]='by air'),DUAL('Air',3),

If([Forwarder 货代]='DHL',DUAL('Express',4),

If([Forwarder 货代]<>'赋友' and [Forwarder 货代]<>'乾运' and [Forwarder 货代]<>'广悦' and [Forwarder 货代]<>'泓明'
and [Forwarder 货代]<>'伊森' and [Forwarder 货代]<>'OTIM' and [Forwarder 货代]<>'HMG' and [Forwarder 货代]<>'EASON'
and [Forwarder 货代]<>'Logwin' and [Forwarder 货代]<>'LOGWIN' and [Forwarder 货代]<>'DHL',DUAL('Rest',5))))))

Then go to the sorting tab, make it custom sorting for this field and check 'Sort numerically'. This will sort this field according to its numeric represantation.

Hope it helps.

[Vince_CH]

@kaanerisen thanks a lot, it works in the ranking.  

however now I encountered another problem with the same topic,  it seems the 'Sea' category includes more shipments which should not.  like picture bottom left side showed is the original databased loaded, where I defined the category of Sea if the fowarder are 'HMG' or 'EASON' or 'OTIM' and transportation methods in 'LCL' or 'FCL'.   And other fowarders unlisted with LCL and FCL should be in category of Rest instead. 

But now,  the strange thing happened, that other fowarders shipments .e.g. 2018-M30, 2018-M31, are also contained in the sea category.  Can you help with this? thanks!

[kaanerisen]

Hi Vince,

Your first dimenison is calculated but others is coming from model itself. 

You should do the category tagging process in the script side. Add this expression, used for calculated dimension, in your script and use the new field instead of calculated dimension on your report.

[Vince_CH]

Hello Kaanerisen , you are right, will try to do the same. I believe it would look more neatly.
but the logic behind should be same, right?
just wondering for category of Sea, why it is adding more shipment not belonged to?
If i put the same in script in instead, this shoud be same ressult with same logic?