10 Replies Latest reply: Aug 1, 2018 7:44 AM by Mohammed Al Radi RSS

    If Statement for Salary Average Taking Different date-fields into account

    Mohammed Al Radi

      Dear Qlik Community,


      I hope this finds you well. I currently have the following expression to calculate 'average monthly salary':



      avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear))


      The expression  works fine if I filter by 'Month Year'. However if I try and filter by [Quarter Year] or [Year] the results are incorrect (average salary per month is the project KPI but it would be nice to be able to see the quarter average salary & year average salary).


      If I change the above expression to:


      avg(aggr(sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], [Quarter Year]))


      Then filtering by quarter works but then month doesn't work...The same is true when I replace the date field with [Year] then only the result for year is accurate and the others are not.


      Is there a way to adjust the expression to take all three date fields into account? I have a bar graph showing monthly average income but if possible I would like to include alternative dimensions for quarter and year to give the client more options.


      Thank you for your support