Error in calculated dimension/expressions and tags in scripts

[Vince_CH]

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

[Vince_CH]

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 · Accepted solution]

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

[Vince_CH]

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

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]

Great.. Please close the thread . 🙂

[Vince_CH]

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]

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.

[Vince_CH]

I see, appreciated, thanks again!