I am working on IDT to create a universe to be used in Design Studio.
The requirement is I have to provide two different measures in single query to be used in design studio, where
Query 1 (Spilled over cases for current month) -->
select sum(Loan_Amt)
from Loan_Table
where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null)
Query 2 (Spilled over cases for previous month) -->
select sum(Loan_Amt)
from Loan_Table
where Login_Date < add_months (Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null)
where Month_Start_Date is the 1st day of the current month. Now suppose if Query 1's answer is 657 and Query 2's is 1200 independently but when both of them are taken together with a dimension say, zone, they are giving similiar results,
ZONES CURR_MONTH PREV_MONTH
West Zone 67.66 67.66
East Zone 5.89 5.89
North Zone 55.65 55.65
South Zone 54.82 54.82
the script generated is -->
select zones, sum(Loan_Amt),
sum(Loan_Amt)
from Loan_Table
where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null) AND Login_Date < add_months(Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null)
group by zones
The script that was expected is -->
select zones,
(select sum(Loan_Amt) where Login_Date < Month_Start_Date AND (Decision_Date >=Month_Start_Date OR Decision_Date is null) ),
(select sum(Loan_Amt) from Loan_Table where Login_Date < add_months (Month_Start_Date,-1) AND (Decision_Date >=add_months(Month_Start_Date,-1) OR Decision_Date is null))
group by zones
I can't attach screenshots due to some reasons but I think I've explained to the best I can. Any ideas would be highly appreciated.