Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
15 Replies
neha_shirsath
Specialist
Specialist

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;

Anil_Babu_Samineni

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;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
trspaete
Contributor II
Contributor II

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.

xyz1
Creator III
Creator III
Author

Thanks Neha

regards

xyz1
Creator III
Creator III
Author

.

Anil_Babu_Samineni

NP, Always welcome if you mark Helpful for others

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)