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

       

      Best,

       

      Mohammed