0 Replies Latest reply: Mar 18, 2018 7:14 PM by Rachel Zhong RSS

    Pivot sheet read data from two tables

    Rachel Zhong

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