Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

xiyuzw1113
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)
Community Browser