Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
malradi88
Creator II
Creator II

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

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

10 Replies
malradi88
Creator II
Creator II
Author

Hi Sunny

Sorry I meant that in the bar chart I am using with 'average salary' as a measure and 'Month Year' as the principal dimension (with QuarterYear & Year as alternative dimensions) is there an expresison I can put that would make the visualisation produce accurate average salary results when I choose the alternative date dimensions?

Currently, this expression  works perfectly for a  KPI object, or when I  select a date via a filter pane:

If(GetSelectedCount([Year]) > 0,

(Avg(Aggr(Sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], Year))),

If(GetSelectedCount([QuarterYear]) > 0,

(Avg(Aggr(Sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], [QuarterYear]))),

(Avg(Aggr(Sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear)))))

but within the bar graph itself when I switch to quarter year or year via alternative dimension it does not work (for some reason it only works when I select a date field via filterpane). Currently only monthyear works as it has been set as the default using the if(getselectcount expression.

Could the 'Get Select Count' be replaced with 'Getobjectfield' in the bar chart measure so that filtering via alternative dimension produces accurate results? e.g. if getobjectfield(2), year, ifgetobjectfield (3), quarter year ?  I tried doing it but I can't get the right syntax

Thank you for your support Sunny Sorry if I am not explaining it clearly!

Best,

Mohammed