Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Vince_CH
Contributor II

Error in calculated dimension/expressions and tags in scripts

Dear Friends,

In making one logistic analysis, with source data like following picture showed in excel list, I am trying to create some category in 'Ground, Sea, Air, Express, Rest".  ('Rest' is to count all shipments not paid by our company, while other categories are paid by us).

I have created a tag as Type in script as following writtings, then use Type as dimension of pivot table, measures as  "Count({$<FiscalYear={'$(=Max(FiscalYear))'}>}distinct[Serial 序列号])",  now it works well with 'Ground, Sea, Air', but not correct with 'Express' and ' Rest', as below bold fonts. 

To cross check above, I also made one bar chart as below picutre indicated, using set expressions, to count the same of 'Express' and 'Rest', both of them are in same result as pivot table, not correct.  Are there any experts who can help to sort out this problem? thanks.

---------------------------------------------------------------------------------------------------------------------------------

If([Transport methods]='Chartered truck' or [Transport methods]='Bulk truck',Dual('Ground',1),

If([[Transport methods]]='LCL' or [[Transport methods]]='FCL',

If([Forwarder]='OTIM' or [Forwarder]='HMG' or [Forwarder]='EASON',Dual('Sea',2),Dual('Rest',5)),

If([[Transport methods]]='by air',

If([Forwarder]='HMG' or [Forwarder]='EASON' or [Forwarder]='Logwin',Dual('Air',3),Dual('Rest',5)),

If([[Transport methods]]='Express',

If([Forwarder]='DHL',

If([Freight (Overseas)]<>Null() and [Freight (Overseas)]<>0,Dual('Express',4),Dual('Rest',5)),Dual('Rest',5)),

Dual('Rest',5))))) as Type

11.jpg

1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

If([Transport methods]='Chartered truck' or [Transport methods]='Bulk truck',Dual('Ground',1),

If([[Transport methods]]='LCL' or [[Transport methods]]='FCL',

If([Forwarder]='OTIM' or [Forwarder]='HMG' or [Forwarder]='EASON',Dual('Sea',2),Dual('Rest',5)),

If([[Transport methods]]='by air',

If([Forwarder]='HMG' or [Forwarder]='EASON' or [Forwarder]='Logwin',Dual('Air',3),Dual('Rest',5)),

If([[Transport methods]]='Express',

If([Forwarder]='DHL',

If(isnull([Freight (Overseas)])=0 and [Freight (Overseas)]<>0,Dual('Express',4),Dual('Rest',5)),Dual('Rest',5)),

Dual('Rest',5))))) as Type
Learning never stops.
8 Replies
Vince_CH
Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

Hello Experts, sorry for the long post here, just intend to desribe the issue throughout. hope you can give some hint or other alternative effective solutions? thanks
pradosh_thakur
Honored Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

If([Transport methods]='Chartered truck' or [Transport methods]='Bulk truck',Dual('Ground',1),

If([[Transport methods]]='LCL' or [[Transport methods]]='FCL',

If([Forwarder]='OTIM' or [Forwarder]='HMG' or [Forwarder]='EASON',Dual('Sea',2),Dual('Rest',5)),

If([[Transport methods]]='by air',

If([Forwarder]='HMG' or [Forwarder]='EASON' or [Forwarder]='Logwin',Dual('Air',3),Dual('Rest',5)),

If([[Transport methods]]='Express',

If([Forwarder]='DHL',

If(isnull([Freight (Overseas)])=0 and [Freight (Overseas)]<>0,Dual('Express',4),Dual('Rest',5)),Dual('Rest',5)),

Dual('Rest',5))))) as Type
Learning never stops.
Vince_CH
Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

@pradosh_thakur  thanks for the kindly reply.

if I am right, that you had modified " If(isnull([Freight (Overseas)])=0 and [Freight (Overseas)]<>0,...", right?

I just tried with this, and 'Express' is still showing counts 34, actaully it should be 13 only.  Rest is in same disorder too. 

Vince_CH
Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

Hello Pradosh, I crossed checking the source data and there was some data just updated and I was not awared of that, so the counts is correct as you suggested. thanks a lot!
pradosh_thakur
Honored Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

Great.. Please close the thread . 🙂
Learning never stops.
Vince_CH
Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

I just closed it. By the way, just wondering the logic your modification behind? if possible, can you explain a little more? thanks
pradosh_thakur
Honored Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

Yeah .. I couldn't see anything wrong in your expression other than a doubt that might be comparing a filed to null() to be the issue so modified it to isnull(field) =0. IsNULL returns -1 when null else 0.
Learning never stops.
Vince_CH
Contributor II

Re: Error in calculated dimension/expressions and tags in scripts

I see, appreciated, thanks again!