Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I having scenario to do aggregation at chart and pivot table level. I have created (Month, Quarterly, Half yearly and Yearly) base on the date field .
Response No | Project ID | Date | Account | Sub Account | Tower | Sub Tower | Response % |
23307 | 3849 | 02-Apr-2020 | Good wills | AB | MLD | DIV | 80.00% |
23702 | 3849 | 06-Apr-2020 | Good wills | AB | MLD | DIV | 100.00% |
23767 | 6046 | 14-Apr-2020 | Good wills | SC | MLD | DIV | 100.00% |
23813 | 12000 | 28-Apr-2020 | Good wills | ALL | MLD | DIV | 60.00% |
23821 | 10374 | 21-Apr-2020 | Good wills | HA | MLD | DIV | 80.00% |
23934 | 10149 | 12-May-2020 | Good wills | LP | MLD | DIV | 80.00% |
23952 | 10974 | 07-May-2020 | Good wills | GMBH | MLD | DIV | 100.00% |
24004 | 7312 | 14-May-2020 | Good wills | BE | MLD | DIV | 100.00% |
24119 | 10899 | 29-May-2020 | Good wills | BE | MLD | DIV | 100.00% |
24120 | 10836 | 27-May-2020 | Good wills | BE | MLD | DIV | 100.00% |
24131 | 8622 | 02-Jun-2020 | Good wills | LP | MLD | DIV | 80.00% |
24135 | 8853 | 05-Jun-2020 | Good wills | BE | MLD | DIV | 100.00% |
24136 | 11314 | 28-May-2020 | Good wills | LAE | MLD | DIV | 80.00% |
24143 | 9408 | 28-May-2020 | Good wills | MO | MLD | DIV | 80.00% |
24148 | 4388 | 29-May-2020 | Good wills | DM | MLD | DIV | 80.00% |
24150 | 11387 | 29-May-2020 | Good wills | PAL | MLD | DIV | 80.00% |
24151 | 11548 | 02-Jun-2020 | Good wills | PAL | MLD | DIV | 100.00% |
24153 | 4802 | 01-Jun-2020 | Good wills | VID | MLD | DIV | 80.00% |
24181 | 11769 | 02-Jun-2020 | Good wills | DM | MLD | DIV | 80.00% |
24192 | 9752 | 04-Jun-2020 | Good wills | BE | MLD | DIV | 80.00% |
24202 | 9752 | 08-Jun-2020 | Good wills | BE | MLD | DIV | 100.00% |
24242 | 2574 | 17-Jun-2020 | Good wills | VID | MLD | DIV | 100.00% |
24249 | 12609 | 16-Jun-2020 | GRBT | HO | MLD | DIV | 80.00% |
24403 | 9138 | 09-Jul-2020 | Good wills | BE | MLD | DIV | 100.00% |
24474 | 10761 | 02-Jul-2020 | Good wills | BE | MLD | DIV | 100.00% |
24611 | 10852 | 09-Jul-2020 | Good wills | BE | MLD | DIV | 100.00% |
24638 | 10149 | 24-Jul-2020 | Good wills | LP | MLD | DIV | 80.00% |
24691 | 6046 | 19-Jul-2020 | Good wills | SC | MLD | DIV | 100.00% |
24739 | 12649 | 31-Jul-2020 | Good wills | GMBH | MLD | DIV | 80.00% |
24971 | 11654 | 20-Aug-2020 | Good wills | HA | MLD | DIV | 80.00% |
24979 | 12345 | 31-Aug-2020 | Good wills | MO | MLD | DIV | 80.00% |
24980 | 7636 | 21-Aug-2020 | Good wills | LP | MLD | DIV | 80.00% |
25068 | 3849 | 31-Aug-2020 | Good wills | AB | MLD | DIV | 100.00% |
25115 | 5514 | 31-Aug-2020 | Good wills | LE | MLD | DIV | 80.00% |
25223 | 10836 | 16-Sep-2020 | Good wills | BE | MLD | DIV | 100.00% |
25262 | 12000 | 21-Sep-2020 | Good wills | AL | MLD | DIV | 60.00% |
25299 | 11387 | 24-Sep-2020 | Good wills | PAL | MLD | DIV | 80.00% |
25501 | 6046 | 12-Oct-2020 | Good wills | SC | MLD | DIV | 100.00% |
25541 | 12914 | 12-Oct-2020 | Good wills | DM | MLD | DIV | 100.00% |
25658 | 11314 | 20-Oct-2020 | Good wills | LAE | MLD | DIV | 100.00% |
25736 | 3849 | 28-Oct-2020 | Good wills | AB | MLD | DIV | 100.00% |
I have to create pivot table with these fields except Response No. but am not able to get response % correctly
What is your current not correct output and what is your desired output? Please explain.
i have created pivot table with this data
Dimensions : Project ID, Account, Sub Account, Tower, Sub Tower, Month, Qtr, Half year and year
expression : Avg(Aggr(Avg(Response %),Project ID, Account, Sub Account, Tower, Sub Tower, Month, Qtr, Half year , year))
Base on this expression the rollup % values are not coming correctly
Hi @Leninond
Hope Response No is primary field, in that case, try like below
Avg(Aggr(Avg(Response %),[Response No]))
Hi Mayil Vahanan,
[Response no] is granular low level data and primary field . I have tried Average value is not showing correctly.