Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Vince_CH
Contributor II

Conditional dimension ranking in pivot table

 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')))))

11.jpg

 

1 Solution

Accepted Solutions
kaanerisen
Contributor III

Re: Conditional dimension ranking in pivot table

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.

6 Replies
OmarBenSalem
Esteemed Contributor II

Re: Conditional dimension ranking in pivot table

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
Contributor II

Re: Conditional dimension ranking in pivot table

@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
Contributor III

Re: Conditional dimension ranking in pivot table

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
Contributor II

Re: Conditional dimension ranking in pivot table

@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!

 

11.jpg

kaanerisen
Contributor III

Re: Conditional dimension ranking in pivot table

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
Contributor II

Re: Conditional dimension ranking in pivot table

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?