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: 
elie_issa
Creator II
Creator 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
sunny_talwar

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

elie_issa
Creator II
Creator II
Author

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.

sunny_talwar

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
Creator II
Creator II
Author

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 ?

sunny_talwar

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
Creator II
Creator II
Author

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

sunny_talwar

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
Creator II
Creator II
Author

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.