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
sunny_talwar

What happens when you add all of them?

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

malradi88
Creator II
Creator II
Author

Hi Sunny

If I put all three date fields the month stays correct and the others don't.

For example, in the data set I currently only have 3 months of data:

April average salary: 226

May average salary: 640

June average salary: 359

If I switch dimension to quarter then the quarter average salary just becomes 408 (which is just the average of the 3 above stated months).

Best,

Mohammed

sunny_talwar

Difficult to say without seeing, but may be you need this

If(GetSelectedCount(MonthYear) > 0,

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

If(GetSelectedCount([Quarter Year]) > 0,

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

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

malradi88
Creator II
Creator II
Author

Thank you very much for your support Sunny It is now giving a correct 'QuarterYear' but not month year. I think you have put me on the right track though.  the >0 basically means 'the field has been selected' right?

best,

Mohammed

sunny_talwar

Yes it does

malradi88
Creator II
Creator II
Author

Hi Sunny

So I switched it around to this :

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

making 'Month Year' the default. I think tried filtering with Quarter year (from a filter pane) and the same for year and it works . But, if don't use the filters and instead just switch to an 'alternative dimension' (month year to quarter year in the bar graph) it doesn't work. Is there something like getselectcount that works when I switch date dimensions in a bar chart?

Best,

Mohammed

sunny_talwar

May be use GetObjectField() function to do this

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

Make sure to change 1 to whichever number dimension Year/MonthYear/QuarterYear will be...

malradi88
Creator II
Creator II
Author

Hi Sunny, how would I integrate it into the if-functio? I tried a few combos but my syntax is wrong

Thank you for your help!

Best,

Mohammed

sunny_talwar

Into the if statement? I am not sure I follow... do you have an alternate dimension or do you have another way to select a dimension? May be share the exact business scenario here....