Expression data does not correspond to Dimension value

[#1]

Hi,

I'm trying to create a table that shows top 2 applicant countries but I'm having trouble determining why the pivot table data created as Expression does not display the correct values.  The table shows following data

Term	Total Count	International
2014 Summer	1664	89 China, 17 South Korea
2014 Fall	1484	45 China, 5 South Korea
2015 Spring	423	2 China, 0 South Korea

but the correct values are only displayed when I select a particular term.  So if I select "2014 Fall," then the correct values for International is displayed

Term	Total Count	International
2014 Fall	1484	45 China, 10 Brazil

Currently the Term comes from a key INLINE LOAD fact table of the terms shown in the 1st table, then I have a load table with applicants' ID and Country.

I created the following variables to determine most frequent Country string and the second most frequent:

=FirstSortedValue(DEMO_PS_APP_CITIZ_CNTRY,-(aggr(count({$<DEMO_PS_APP_CITIZ_CNTRY-={"UNITED STATES"}>}DEMO_PS_APP_ID), DEMO_PS_APP_CITIZ_CNTRY)))

and

=FirstSortedValue(DEMO_PS_APP_CITIZ_CNTRY,-(aggr(count({$<DEMO_PS_APP_CITIZ_CNTRY-={"UNITED STATES"}>}DEMO_PS_APP_ID), DEMO_PS_APP_CITIZ_CNTRY)),2)

Then the International column data is filled using the following Expression:

=count({<DEMO_PS_APP_CITIZ_CNTRY={"$(vInternationalMax_AP)"}>}DEMO_PS_APP_ID)& ' ' & vInternationalMax_AP & ', ' &

count({<DEMO_PS_APP_CITIZ_CNTRY={"$(vInternationalMax2_AP)"}>}DEMO_PS_APP_ID)& ' ' & vInternationalMax2_AP

I know that somewhere in the Variables or Expressions I need to tie the Term dimension but just have not been successful.  Any help will be much appreciated!!

Also, in the load process, the Applicants table field of Term is mapped to key Term table as 'App_Term' AS 'keyTerm'.  This was done because there are other tables (admits, matrics, etc.) that need to use the same key Term fact table.

[Gysbert_Wassenaar · Accepted solution]

Can you post a qlikview document that demonstrates the problem?

[#3]

Hi Gysbert,

Thanks for the offer to take a look at the problem.  I was able to create a qvw file that mimics the issue.

Thanks,

Aricin

[Gysbert_Wassenaar]

Ok, the expressions in the variables start with a = character. That means they're calculated outside the context of the chart. They'll have the same value wherever they are used in the document. When you select a Term, then the variable is recalculated and will consider only the Term you selected. So the values of the variables will change like you noticed.

[#5]

Thanks for the comment, Gysbert.

I removed the leading "=" from the variables, now the Expressions return no values by default or when I select a term.   Any other suggestion on how to go about fixing my problem?

Thanks,

Aricin