[null, null, {"document_url": "https://community.qlik.com/t5/New-to-Qlik-Sense/Pivot-sheet-read-data-from-two-tables/td-p/45420", "unformatted_src": "/legacyfs/online/196656_Capture%2011.PNG", "src": "https://community.qlik.com/legacyfs/online/196656_Capture%2011.PNG", "formatted_filename": "Capture", "alt_text": "Capture 11.PNG"}, null]
New to Qlik Sense

Discussion board where members can get started with Qlik Sense. New Contributor III

Pivot sheet read data from two tables

Hi all Qlik experts, I have encountered a problem about reading data from two tables.

My Pivot table is below: The filed 'No of respondent' comes from two tables: The 'Event overview' and aggregated value from another table.

Now I would like to achieve this function:

If the year of 'start date' is before 2018, then we use the value come from 'Event overview';

If the year of 'start date' is 2018 or later, then

'No of respondent' = count({<[Custom workshop.event]='GetFieldSelection(Event)'>+<[Custom workshop.event]>}distinct [Custom workshop.Respondent_No]), which comes from another table.

And I would like to display the average value in total row, I have tried expression like this:

=if(Year([Start Date])<2018, avg(Workshop_Respondents), avg(aggr(count({<[Custom workshop.Event]='GetFieldSelection(Event)'>}distinct [Custom workshop.Respondent_No]),Event)))

And can't get the value I want, any suggestions?? Thanks so much!!

Tags (1)
View All &gg;
Community Browser