Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elie_issa
Contributor II

Avg of expression for the last 26 weeks

Hi Guys,

I need to get the average of the last 26 weeks of my expression from the date i am filtering on.

Check the attached screen where i am generating my expression by week but in my KPI I should show the average for the last 26 weeks.

Thanks.

8 Replies

Re: Avg of expression for the last 26 weeks

last 26 weeks based on selection? or most recent 26 weeks based on current date?

elie_issa
Contributor II

Re: Avg of expression for the last 26 weeks

If we didn't make any selection we need to get the average of the last 26 weeks from today's date.

For example if we make a selection and we choose March 2018 we need to get the average of the last 26 weeks before the 1st of March 2018.

Re: Avg of expression for the last 26 weeks

I would do something like this

Avg({<[Master Calendar Date] = {"$(='>=' & Date(Max(Date) - 182) & '<=' & Date(Max(Date)))"}. Quarter, Month, MonthYear, Week, WeekYear, Year>} [Nationalization Ratio])

elie_issa
Contributor II

Re: Avg of expression for the last 26 weeks

Avg({<[Master Calendar Date] = {"$(='>=' & Date(Max(Date) - 182) & '<=' & Date(Max(Date)))"}. Quarter, Month, MonthYear, Week, WeekYear, Year>} [Nationalization Ratio])


Could you please clarify what is the purpose to insert the below part in the above expression :


. Quarter, Month, MonthYear, Week, WeekYear, Year 


Also do you think it will work if we replace the [Nationalization Ratio] by a Variable ?

Re: Avg of expression for the last 26 weeks

Quarter, Month, MonthYear, Week, WeekYear, Year

Just ignoring selections in these fields

Also do you think it will work if we replace the [Nationalization Ratio] by a Variable ?

Is there a aggregation like Sum, Avg, Min, Max, or something like that within the variable?

elie_issa
Contributor II

Re: Avg of expression for the last 26 weeks

i am using count distinct in my variable and the expression contain set analysis also.

Re: Avg of expression for the last 26 weeks

Then you might need an Aggr() function... but difficult to say without knowing too much about what you have... would you be able to share a sample to help you better?

elie_issa
Contributor II

Re: Avg of expression for the last 26 weeks

Nationalization Expression:

$(vNationalizationSA)

/

(Count({

<

  [Current Employee Flag] = {'Yes'},

  [Terminated] = {'No'},

  [Assignment Type] = {'E'},

  [Gulf Flag]              = {'No'},

  [Person Type Category] -= {'Daily Hired'}

>

}Distinct [Employee Number]) - $(vNationalizationSACountNum) + $(vNationalizationSA))

the calculation is complicated but basically all variables contain the same concept count distinct employee number.

Hope this helps.

Community Browser