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!!