Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a GP Target table with Targets as Month1, Month2,..........,Month12 . I want to calculate YTD for specified consultant for Month11. So it must sum the values from( Month1+Month2............+Month11). I have achieved to calculate target for current Month. But no success on YTD. I have attached the GP Target Excel File also . So please provide any suggestions ASAP.
you need to use cross table so that you transform month1, month2 columns into rows
then all your months will be under one column named month
as per the excel sheet that you sent
your script will be somehting like this:
test:
LOAD [Emp ID],
Name,
Level,
Location,
[115.71],
A,
B,
C,
[0.15],
A1,
B1,
C1,
A2,
B2,
C2,
F28,
[Our Targets],
Var,
Var%,
AF,
AG,
Month1,
Month2,
Month3,
Month4,
Month5,
Month6,
Month7,
Month8,
Month9,
Month10,
Month11,
Month12
FROM
[folder\Target cascade template.xlsx]
(ooxml, embedded labels, table is [IC spread]);
CrossTable(Month,Value,21)
load * resident test;
drop Table test;