Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.
SALES:
LOAD party_name & '|' & date as Key,
party_name,
sales_amount,
date
FROM
[2months.xlsx]
(ooxml, embedded labels, table is Sheet3);
ORDER:
LOAD party_name & '|' & date as Key,
party_name,
booking_amount,
date
FROM
[2months.xlsx]
(ooxml, embedded labels, table is Sheet4);
LINK_TABLE:
LOAD party_name & '|' & date as Key,
party_name,
date
Resident SALES;
DROP Fields party_name, date From SALES;
Concatenate
LOAD party_name & '|' & date as Key,
party_name,
date
Resident ORDER;
DROP Fields party_name, date From ORDER;
Master_Cal:
Load distinct date ,
//Floor(Claim_Process_Date) as DateNum ,
QuarterStart(date) as QuaterStartDate,
Day(date) as Day,
Month(date) as Month,
Year(date) as Year,
Monthname(date) as MonthYear,
num(month(date)) as Month_Number,
Week(date) as Week,
Month(date) &'-'& Year(date) as YearMonth ,
if(num(month(date))>=4 and num(month(date))<=6,'Q1',
if(num(month(date))>=7 and num(month(date))<=9,'Q2',
if(num(month(date))>=10 and num(month(date))<=12,'Q3',
if(num(month(date))>=1 and num(month(date))<=3,'Q4')))) as Quarter,
if(num(month(date))<=3,Year(date)-1&'-'&right(Year(date),2),Year(date)&'-'&right(year(date)+1,2)) as FinancialYear,
if(num(month(date))<=3,num(month(date))+9,num(month(date))-3) as FinancialMonthNumber
Resident LINK_TABLE;
Don't require again use simple Key
Link_Table:
LOAD Key, party_name, date Resident Sales;
Concatenate
LOAD Key, party_name, date Resident Order;
DROP Fields party_name, date From Sales;
DROP Fields party_name, date From Order;
A little different approach. You wanted to group your results by Quarters. Join the two tables and create a column that calculates the Quarter period based on either the Sales Date or Booking Date of the row. Then Pivot with Party name and Quarter Number with Sums for Sales & Bookings.
Thanks Neha
regards
.
NP, Always welcome if you mark Helpful for others