Qlik Community

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:

Capture 11.PNG

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)