Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Vince_CH
Creator III
Creator III

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

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.

View solution in original post

8 Replies
Vince_CH
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

@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
Creator III
Creator III
Author

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

Great.. Please close the thread . 🙂
Learning never stops.
Vince_CH
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

I see, appreciated, thanks again!