Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What happens when you add all of them?
Avg(Aggr(Sum({<Datetype= {'Date Produced'}>}Remuneration), [Identifier Code], MonthYear, [Quarter Year], Year))
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
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))))
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
Yes it does
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
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...
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
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....