Comparative Analysis

ali_hijazi — Thu, 28 Feb 2013 09:11:21 GMT

Well I got a qlikview document I have a pivot table in which I'm writing comparative analysis expressions

I created to alternate states, Group1 and Group2

my Expression is very simple

sum({Group1}Sales)

however now I want this expression to be affected by selections from all other fields

i.e. Sum({Group1 <Path=$::Path......>Sales)

however the fields are very big in number

I defined the following expression

concat('[' & $Field & ']=$::[' & $Field & ']',',')

if I evaluate this in a text object I get :

[# of Days Late]=$::[# of Days Late],[# of Days to Ship]=$::[# of Days to Ship],[Accuracy]=$::[Accuracy],[Address Number]=$::[Address Number],[AltCounty]=$::[AltCounty],[AltState]=$::[AltState],[BackOrder]=$::[BackOrder],[BudgetKey]=$::[BudgetKey],[BudgetKey]=$::[BudgetKey],[Budget]=$::[Budget],[City]=$::[City],[Community]=$::[Community],[Coordinates]=$::[Coordinates],[Cost]=$::[Cost],[Country Code]=$::[Country Code],[Country]=$::[Country],[County]=$::[County],[Customer Number]=$::[Customer Number],[Customer Number]=$::[Customer Number],[Customer Number]=$::[Customer Number],[Customer]=$::[Customer],[Date]=$::[Date],[Employed Date]=$::[Employed Date],[Employed Year]=$::[Employed Year],[English]=$::[English],[GeoCodeKey]=$::[GeoCodeKey],[GeoCodeKey]=$::[GeoCodeKey],[GrossSales]=$::[GrossSales],[Index]=$::[Index],[Invoice Date]=$::[Invoice Date],[Invoice Number]=$::[Invoice Number],[Item Desc]=$::[Item Desc],[Item Number]=$::[Item Number],[Item Number]=$::[Item Number],[Latitude]=$::[Latitude],[Line of Business]=$::[Line of Business],[Longitude]=$::[Longitude],[Manager Number]=$::[Manager Number],[Manager]=$::[Manager],[Margin]=$::[Margin],[MonthYear]=$::[MonthYear],[Month]=$::[Month],[Open Qty]=$::[Open Qty],[OpenOrder]=$::[OpenOrder],[Order Number]=$::[Order Number],[Path]=$::[Path],[Post Code]=$::[Post Code],[Product Group]=$::[Product Group],[Product Line]=$::[Product Line],[Product Sub Group]=$::[Product Sub Group],[Product Type]=$::[Product Type],[Promised Delivery Date]=$::[Promised Delivery Date],[QtrYear]=$::[QtrYear],[Quarter]=$::[Quarter],[QuotaKey]=$::[QuotaKey],[Region Code]=$::[Region Code],[Region Code]=$::[Region Code],[Region]=$::[Region],[Sales Qty]=$::[Sales Qty],[Sales Rep Name1]=$::[Sales Rep Name1],[Sales Rep Name2]=$::[Sales Rep Name2],[Sales Rep Name3]=$::[Sales Rep Name3],[Sales Rep Name]=$::[Sales Rep Name],[Sales Rep Number]=$::[Sales Rep Number],[Sales Rep Number]=$::[Sales Rep Number],[Sales Rep Number]=$::[Sales Rep Number],[Sales Rep]=$::[Sales Rep],[SalesKey]=$::[SalesKey],[SalesKey]=$::[SalesKey],[SalesRep Profile]=$::[SalesRep Profile],[Sales]=$::[Sales],[State]=$::[State],[Superior Number]=$::[Superior Number],[Superior]=$::[Superior],[Week]=$::[Week],[Weekend]=$::[Weekend],[Weekstart]=$::[Weekstart],[Year]=$::[Year],[_History]=$::[_History],[_LANGUAGE]=$::[_LANGUAGE],[_Projected]=$::[_Projected],[_dimension]=$::[_dimension],[_metricPickOne]=$::[_metricPickOne],[_metricsNo]=$::[_metricsNo],[_metrics]=$::[_metrics],[_zoom_level]=$::[_zoom_level],[maptype]=$::[maptype]

i.e. all the fields

however when I put in my Expression sum({Group1 <$(=concat('[' & $Field & ']=$::[' & $Field & ']',','))>}Sales)

I get null

Please advise

Re: Comparative Analysis

Thu, 28 Feb 2013 12:10:00 GMT

Hello Ali,

Reference App: What's new in QlikView 11.

I guess the issue resides in the association of all these fields with the field "Sales".

For example if you put in a table box both of "# of Days Late" and "Sales" fields, you'll see that there is no association, therefore if you include only this field in the alternate set analysis, you won't get anything.

So what you may need to do is to exclude in the concat function all the fields that are not related to "Sales":

concat({1<$Field -= {'# of Days Late','.....'}>} distinct ...>} Sales)

Hope this helps !

topic Comparative Analysis in QlikView

Comparative Analysis

Re: Comparative Analysis