Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm pretty new to Qlik and this community so i hope i can get advise for solving this calculation problem.
I have a requirement for a KPI (Index) the formula to calculate it looks like this.
Index = ( Cell Value X Grand Total) / (Row total X Column Total)
Other requirements:
> the output of this calculation will be shown in on one pivot table, with only one row dimension.
> The user will have a week period filter in the report where he can select one or more weeks . The input for the formula has to be the value of the max selected week period (like week 5) and calculate max 5 weeks back.
I have tried for the grand total the following formula, but the report 'filters' keep messing up the results.
Grand Total: sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts)
For the Row and Column totals i think an "AGGR" Function is the solution but havent been able to get it working.
I am very thankful for all your advice ...
Greetings
Jose
May be like this?
Sum(Aggr(
(Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} facts) *
Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts))
/
(Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Dimentions> facts) *
Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Week> facts))
, Dimentions, Week))
ah thank you for the fast reply..
I get an output however when i select only one week then it gives me 100% as result.
And i think its only calculating the values from the selected periodes and not going 'back' to a set of max 5 weeks.
I hope this visual helps with the expected output:
gr
How about if you do this
Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} Aggr(
(Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} facts) *
Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL facts))
/
(Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Dimentions> facts) *
Sum({$<week= {">=$min(week(day,0) -5 ))<=$(=max(week(day,0)))"}>} TOTAL <Week> facts))
, Dimentions, Week))