Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a question that I have hit a bit of a wall on. I am trying to move a calculation from a bar chart to the load statement because of the amount of time it takes to load the page. I am needing to join multiple resident tables but can't seem to find the right syntax or whatever.
I need to have the fields from the Report_Table, and each IntervalMatch table to then make a third table; I have tried several different solutions but none create any other tables or they create another $Syn table but they match every other table which is in correct. How can I get what I need?
LET varMinDate = Num(MakeDate(2021)); LET varMaxDate = Floor(num(Today())); CalendarBuild: LOAD $(varMinDate) + Iterno()-1 As Num, Date($(varMinDate) + IterNo() - 1) as TempDate AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); Intervals_30: Load DATE(TempDate-31, 'MM/DD/YYYY') AS From_Date_30 ,DATE(TempDate, 'MM/DD/YYYY') AS To_Date_30 , '30Day_Range' AS IntervalName_30 Resident CalendarBuild; ////////////////////////////////// Intervals_60: Load DATE(TempDate-60, 'MM/DD/YYYY') AS From_Date_60 ,DATE(TempDate, 'MM/DD/YYYY') AS To_Date_60 , '60Day_Range' AS IntervalName_60 Resident CalendarBuild;
[IntervalMatch_30]: IntervalMatch (Trigger_dt) Load distinct From_Date_30, To_Date_30 resident Intervals_30; //////////////////// [IntervalMatch_60]: IntervalMatch (Trigger_dt) Load distinct From_Date_60, To_Date_60 resident Intervals_60;
Returns_Rate_30_Day: NUM(COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_30,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY') AND DATE(Return_Dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'),RecordNum))/COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_30,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_30,'MM/DD/YYYY'),RecordNum)), '#,###.0%') Returns_Rate_60_Day: NUM(COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_60,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY') AND DATE(Return_Dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY') AND DATE(Return_Dt) <> DATE('12/31/1899','MM/DD/YYYY'),RecordNum))/COUNT( distinct IF( DATE(Trigger_dt,'MM/DD/YYYY') > DATE(From_Date_60,'MM/DD/YYYY') AND DATE(Trigger_dt,'MM/DD/YYYY') < DATE(To_Date_60,'MM/DD/YYYY'),RecordNum)), '#,###.0%')