Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
last 26 weeks based on selection? or most recent 26 weeks based on current date?
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.
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])
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 ?
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?
i am using count distinct in my variable and the expression contain set analysis also.
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?
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.