F
Firman-EID
Dear All,
I have problem regarding below master data in creating the pivot :
Site ID Site Name Status Site On air date
Month Target
===================================================
1234 Jhon Stue done 1 jan 06
Jan 06
4567 Albert ongoing
CO fr Jan,Feb (Mar 06)
8910 David ongoing
CO fr Feb (Mar 06)
1112 Silvia done 26 mar 06 CO
fr Jan,Feb (Mar 06)
1314 Alea done 17 Feb 06 CO
fr Jan (Feb 06)
1516 Siliya done 3 Feb 06
Feb 06
1718 Delea done 13 mar 06
Mar 06
1920 Zura done 18 Jan 06
Jan 06
2122 Azuri done 3 mar 06 CO
fr Feb (Mar 06)
Below are the explenation :
case date : assume that we are in March 2006
- CO = Carry over
- Every site thats has not accomplished in its month targetted then will be
carry over to the next month and also becoming the next month targetted.
- there was 5 site targetted in jan 2006 and only 2 sites can be on aired in
its month (jan 2006).
- there was 6 sites targetted in feb 2006 which 3 sites are carried over
from previous month (jan 2006) and at the end only 2 sites can be on air-ed
in its month,remain are carried over to march.
-there was 5 site targetted in march 2006 which 4 site are carried over from
jan and feb and there is only 3 sites succed to on air in its month targetted.
I would like to create 2 pivot table which as follow :
- 1 pivot table that can count total sites targetted in each month target
(grouped by month target --- > jan target = 5 sites , Feb target = 6 sites,
March target = 5) and count how many of them "on air" in its month targetted
(ex : jan target = 5 sites and on air on its month targetted = 2 sites)
- 1 pivot table that can count total sites "on air" in every month (grouped
by month).
ex :
Site on air within Jan 2006 range (from 1 jan to 31 jan 2006) = 2 sites,
site on air on feb 2006 range (from 1 feb to 28 feb 2006) = 2 sites,
site on air in march 2006 range (from 1 march to 31 march 06) = 3 sites.
I can use "sum product" mix with "search" or "find" formula if it is normal
table.
But is it possible pivot can do the same thing so above 2 pivot can be
created ??
Look forward for your reply and thank you for support
Please kindly cc your answer to my email as as well : (e-mail address removed)
Thank you,
Firman
I have problem regarding below master data in creating the pivot :
Site ID Site Name Status Site On air date
Month Target
===================================================
1234 Jhon Stue done 1 jan 06
Jan 06
4567 Albert ongoing
CO fr Jan,Feb (Mar 06)
8910 David ongoing
CO fr Feb (Mar 06)
1112 Silvia done 26 mar 06 CO
fr Jan,Feb (Mar 06)
1314 Alea done 17 Feb 06 CO
fr Jan (Feb 06)
1516 Siliya done 3 Feb 06
Feb 06
1718 Delea done 13 mar 06
Mar 06
1920 Zura done 18 Jan 06
Jan 06
2122 Azuri done 3 mar 06 CO
fr Feb (Mar 06)
Below are the explenation :
case date : assume that we are in March 2006
- CO = Carry over
- Every site thats has not accomplished in its month targetted then will be
carry over to the next month and also becoming the next month targetted.
- there was 5 site targetted in jan 2006 and only 2 sites can be on aired in
its month (jan 2006).
- there was 6 sites targetted in feb 2006 which 3 sites are carried over
from previous month (jan 2006) and at the end only 2 sites can be on air-ed
in its month,remain are carried over to march.
-there was 5 site targetted in march 2006 which 4 site are carried over from
jan and feb and there is only 3 sites succed to on air in its month targetted.
I would like to create 2 pivot table which as follow :
- 1 pivot table that can count total sites targetted in each month target
(grouped by month target --- > jan target = 5 sites , Feb target = 6 sites,
March target = 5) and count how many of them "on air" in its month targetted
(ex : jan target = 5 sites and on air on its month targetted = 2 sites)
- 1 pivot table that can count total sites "on air" in every month (grouped
by month).
ex :
Site on air within Jan 2006 range (from 1 jan to 31 jan 2006) = 2 sites,
site on air on feb 2006 range (from 1 feb to 28 feb 2006) = 2 sites,
site on air in march 2006 range (from 1 march to 31 march 06) = 3 sites.
I can use "sum product" mix with "search" or "find" formula if it is normal
table.
But is it possible pivot can do the same thing so above 2 pivot can be
created ??
Look forward for your reply and thank you for support
Please kindly cc your answer to my email as as well : (e-mail address removed)
Thank you,
Firman