-
1. Re: How can I calculate % of total using filters?
Norbert MaijoorMay 17, 2017 4:47 AM (in response to Karolina Skvortsova)
Hi Karolina,
Find my approach as alternative below and stored in attached workbook version 9.3 located in the original thread
1. % of: sum([Task Date])/{fixed:sum ([Task Date])}
Hope it helps.
Regards,
Norbert
-
% fixed_nalmai.twbx 21.4 KB
-
-
2. Re: How can I calculate % of total using filters?
Karolina Skvortsova May 17, 2017 5:52 AM (in response to Norbert Maijoor)1 of 1 people found this helpfulThank you so much for your great explanation and detailed example. However, I need to demonstrate my data by months (since August 2016), as a result, when I use the following formula in denominator of "% of" I have huge amount. How can I fix the total amount by months?
In the denominator of the field "% of" I use the following formula {fixed:COUNT([Task Date])}
-
3. Re: How can I calculate % of total using filters?
Norbert MaijoorMay 17, 2017 6:02 AM (in response to Karolina Skvortsova)
1 of 1 people found this helpfulHi Karolina
Something like
{fixed Month(Task Date): sum(%of)} should do the trick;)
Regards,
Norbert
-
4. Re: How can I calculate % of total using filters?
Karolina Skvortsova May 17, 2017 8:30 AM (in response to Norbert Maijoor)1 of 1 people found this helpfulNorbert, thank you so much for your help! I've applied the following formula and unfortunately, it hasn't been worked.
{FIXED MONTH([Task Date]): SUM({fixed:COUNT([Task Date])})}
I also get 4767 instead of 165
-
5. Re: How can I calculate % of total using filters?
Karolina Skvortsova May 17, 2017 8:37 AM (in response to Norbert Maijoor)Also the problem is related to aggregation as I need to calculate the number of tasks as COUNT([Task Date])
And, as a result, I need to aggregate denominator as SUM({fixed:COUNT([Task Date])})
Can I avoid this problem with aggregation?
In your example the following formula without aggregation works great: [Task Date]/{fixed:sum([Task Date])}
-
6. Re: How can I calculate % of total using filters?
Norbert MaijoorMay 17, 2017 9:17 AM (in response to Karolina Skvortsova)
Hi Karolina,
Can you share your workbook?
Regards,
Norbert
-
7. Re: How can I calculate % of total using filters?
Karolina Skvortsova May 17, 2017 11:50 AM (in response to Norbert Maijoor)Thank you so much for your great help! Now this formula works:
SUM(INT(NOT(ISNULL([Task Date]))))/SUM({fixed MONTH([Task Date]):SUM(INT(NOT(ISNULL([Task Date]))))})