Preceding or Resident Load

[niha]

Hello,

I have the following table .  First three table are for Quarter 3, quarter 2 and Quarter 1 data. I concatenated all to get a one table say Quarter3. My aim is to find sum of Total hours of  "Total hours(Q3)"+ "Total Hours(Q1)" + "Total Hours(Q2)" as Totalhours . I tried preceding load but gave error. Please suggest.

Load*,
sum("Total hours(Q3)")+sum("Total Hours(Q2)") + sum("Total Hours(Q1)") as Totalhours;

[ Quarter3]:

LOAD
Country,
"Full Name",
Position,
SubCategory,
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" ,

FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate

[QuarterQ2]:

LOAD
Country ,
"Full Name",
Position,
SubCategory,
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)",
Quarter,
FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
[QuarterQ1]:

LOAD
Country ,
"Full Name",
Position,
"Sub Area",
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)"
Quarter,

FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Region_country:
LOAD
Region,
Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks,

Niha

[tchovanec]

It looks like you are missing the group by clause

[JGMDataAnalysis]

Test:
NOCONCATENATE
LOAD
    Country,
    "Full Name",
    Position,
    "Sub Area",
    "Task Name",
    "2019/01",
    "2019/02",
    "2019/03",
    "Total Hours(Q1)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
CONCATENATE (Test)
LOAD
    Country,
    "Full Name",
    Position,
    SubCategory,
    "Task Name",
    "2019/04",
    "2019/05",
    "2019/06",
    "Total Hours(Q2)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
CONCATENATE (Test)
LOAD
    Country,
    "Full Name",
    Position,
    SubCategory,
    "Task Name",
    "2019/07",
    "2019/08",
    "Total hours(Q3)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Region_country:
NOCONCATENATE
LOAD Region, Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);

[niha]

Thanks. 

"Total Hours(Q1)" AS TotalHours,should be TotalhoursQ1, "Total Hours(Q2)" AS TotalHours, should be TotalhourQ2, "Total Hours(Q3)" AS TotalHours, should be TotalhoursQ3 as all have different values . It is the average of each quarter. My aim is to find Total year hours for which I need to sum all three quarters. Now I did preceding load on each table and concatenate it which is working fine. My aim is to get Total hours of all three quarter in script. I want to get TotalYrhours. when I get preceding load above [AllTask Quarter3] I am getting error Q2hours not found.

/Load*,
//sum("Q3hours")+sum("Q2hours") + sum("Q1ours") as TotalYrhours;

[AllTask Quarter3]:
LOAD
Country,
"Full Name",
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" as Q3hours,
FM,
"Therapeutic Area";

LOAD
Country,
"Full Name",
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" ,
FM,
"Therapeutic Area"
FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate
LOAD
Country ,
"Full Name",
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)" as Q2hours,
Quarter,
FM,
"Therapeutic Area";

[AllTask QuarterQ2]:

LOAD
Country ,
"Full Name",
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)",
Quarter,
FM,
"Therapeutic Area"

FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
[AllTask QuarterQ1]:
LOAD
Country ,
"Full Name",
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)" as Q1hours,
Quarter,
FM,
"Therapeutic Area";

LOAD
Country ,
"Full Name",
Position,
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)",
Quarter,
FM,
"Therapeutic Area"
FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Region_country:
LOAD
Region,
Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thnaks,

Niha

[JGMDataAnalysis]

Using the structure of the script that I gave you the only thing you would have to do is at the FrontEnd level is to use this measure: Sum (TotalHours).

/Load*,
//sum("Q3hours")+sum("Q2hours") + sum("Q1ours") as TotalYrhours;

[AllTask Quarter3]:

...

This makes no sense. When using the Sum function in this context you must use a GROUP BY clause. In addition, with the * you are including all the fields in the table which you should pass to GROUP BY.
What is the purpose of calculating the total in script?

[niha]

Hello,

Thanks. The purpose is:

1.  To see total hours isn each quarter which I can do by SUM ( Totalhour (Q1)) so on....

2. I also want to know Total hours of all Quarter say  Yearhours . It is because I have to do some advance set analysis.

lets see below examples. This gives me percentage of hours for a particular task and field for Quarter3. This is the measure. Dimension is region.

Sum({<Field= {ML}, [Task Name] = {[interaction]}>}[Total hours(Q3)])/Sum({<Field= {ML}>}[Total hours(Q3)]) 

Now instead of Q3 hours I want to do it for all year which is SUM of all Quarter hours. I can then take Quarter in my filterpane and can see for all quarter. I am expecting a Barchart with region as dimension and measure as above but instead of Q3 quarter I want whole year. so I want to replace Q3 with Q1+Q2+Q3.

hope this helps.

Regards,

Niha

[niha · Accepted solution]

I,

Finally I came to a conclusion to do set analysis individually. 

=(sum({<FM ={'MSL'}, [Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q1hours)+sum({< FM ={'MSL'},
[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q2hours)+ sum({<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q3hours))
/
(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours))

Thanks,

Niha