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