Using Tablenames in a measure and getting percentage for barchart

[bruce_sorge]

Hello community,

I have something that is probably very easy to do, and I am probably overthinking it. First item: I have 4 tables (Academic, Non-Academic, State and Tribal. What I am trying to do is use these to populate a measure for a bar-chart. I am trying to use this in an expression, but I get an error that if requires 2-3 parameters:

if( valueList('$Table')= 'Academic'
, num(sum(AcadamicTotal)/sum(TribalTotal+StateTotal+NonAcademicTotal),'#,##0%')
OR
valueList('$Table')= 'Non_Acedemic'
, num(sum(NonAcademicTotal)/sum(TribalTotal+StateTotal+AcadamicTotal),'#,##0%')
OR
valueList('$Table')= 'State'
, num(sum(NonAcademicTotal)/sum(TribalTotal+NonAcademicTotal+AcadamicTotal),'#,##0%'),

IF(valueList('$Table')= 'Tribal',
num(sum(TribalTotal)/sum(StateTotal+NonAcademicTotal+AcadamicTotal),'#,##0%')
)
)

Secondly, for my dimension, I am using these tables to populate it, but I also have two other tables that I would like to exclude. I am attaching a screenshot to show you what I am talking about.

Thanks and happy holidays

[JordyWegman]

Hi Bruce,

I think the problem lies in the execution of the variables.

if( valueList('$(Table)')= 'Academic'
, num(sum(AcadamicTotal)/sum(TribalTotal+StateTotal+NonAcademicTotal),'#,##0%')
OR
valueList('$(Table)')= 'Non_Acedemic'
, num(sum(NonAcademicTotal)/sum(TribalTotal+StateTotal+AcadamicTotal),'#,##0%')
OR
valueList('$(Table)')= 'State'
, num(sum(NonAcademicTotal)/sum(TribalTotal+NonAcademicTotal+AcadamicTotal),'#,##0%'),

IF(valueList('$(Table)')= 'Tribal',
num(sum(TribalTotal)/sum(StateTotal+NonAcademicTotal+AcadamicTotal),'#,##0%')
)
)

For the exclusion of the two, make sure you uncheck the box that says Include zero values. Then these dimensions will be automatically dropped.

Jordy

Climber 

[bruce_sorge]

Jo Jordy,

I did uncheck the box that says include zero values, but still the same because these two tables have data. Also the original issue still occurs.

Thanks

Bruce

[rwunderlich]

You syntax error re 2-3 parameters is coming from the ValueList() function.  However, I think your approach to the problem is incorrect (although I may be misunderstanding your objective).  In a Qlik expression, you cannot limit a field to a specific table.  If you want the "NonAcademicTotal" field only from the "State" table you have two choices I can think of:

1. Give the field a different name eg "State_NonAcademicTotal"  in the state table. 

2. Concatenate all the tables. Add a field named "Source", assign the values "State", "Acedemic", etc corresponding to the source.  Use set analysis to filter only rows for the desired source:

num(sum({<Source={'State'}>}NonAcademicTotal)/sum({<Source={'State'}>}TribalTotal+NonAcademicTotal+AcadamicTotal),'#,##0%'),

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[bruce_sorge]

Hi Rob and thank you for your response.

The tables are broken down by grant types (Academic, non-academic, tribal and state). I have sheets that have information based on each grant type (A table and a pie chart that breaks the grants down by state and that states percentage of grants). the final sheet is a roundup where I have a table that has the state name and number of grants that the state was awarded, and a bar chart that is supposed to show the percentage of grants by type. Does this help  clear my objective up? I am still relatively new to Qlik.

Thanks,

Bruce

[rwunderlich · Accepted solution]

I see where you are going. Can you post a few rows of sample data from each file?

-Rob

[bruce_sorge]

Hi Rob,

Thanks for the reply. I am attaching the spreadsheet with the different sheets. On the first sheet - Academic- I have the information that I am trying to reproduce in the bar chart.

Thanks,

Bruce

[rwunderlich]

Hi Bruce,

I don't see multiple sheets in this xls. Is this the correct file?

-Rob

[bruce_sorge]

Wrong spreadsheet.

[rwunderlich · Accepted solution]

Hi Bruce,

The typical Qlik approach would be to load all data from the sheets into a single table, adding an "Institution Type" field to differentiate each.  Then "Institution Type" would be your chart dimension and the chart measure for a count of grants would be:

Count([EPA ID])

or for a Pct of grants:

Count([EPA ID]) / Count(TOTAL [EPA ID])

See the attached qvf for a sample script and chart. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com