setting up Variable using SQL stm

[Mur]

Hello,

I have a WHERE clause in my data load editor which filters my data to show most  recent Year Period. Is it possible to create a variable using that WHERE clause? because I want to use this variable in my sheet within a set analysis.

here is my SQL where clause:

WHERE (FY=
(SELECT YEAR + '-' + RIGHT('0' + CONVERT(VARCHAR(6), PERIOD), 2) AS 'yyyy-mm'
FROM Table_one
WHERE (PERIOD_END_DT =
(SELECT MAX(PERIOD_END_DT)
FROM Table_Two
WHERE (STATUS = 'NOT ACTIVE')))))

this is what I am trying to do but it shows error

LET VYearPeriod= SQL SELECT WHERE (FY=
(SELECT YEAR + '-' + RIGHT('0' + CONVERT(VARCHAR(6), PERIOD), 2) AS 'yyyy-mm'
FROM Table_one
WHERE (PERIOD_END_DT =
(SELECT MAX(PERIOD_END_DT)
FROM Table_Two
WHERE (STATUS = 'NOT ACTIVE')))

[Scotchy · Accepted solution]

Hi Mur,

That just puts a sql string into a variable that will not be executed in your SA expression, hence the error.

You can do all that in the loader, i.e. Extract the value for YearPeriod for your variable there and thus use it anywhere in your app, code sample below ...

// SQL query to get the period date
SQL SELECT YEAR + '-' + RIGHT('0' + CONVERT(VARCHAR(6), PERIOD), 2) AS 'yyyy-mm'
FROM Table_one
WHERE PERIOD_END_DT = (
SELECT MAX(PERIOD_END_DT)
FROM Table_Two
WHERE STATUS = 'NOT ACTIVE'
);

// Load the result into a temporary table
PeriodDateTable:
LOAD [yyyy-mm]
SQL SELECT YEAR + '-' + RIGHT('0' + CONVERT(VARCHAR(6), PERIOD), 2) AS 'yyyy-mm'
FROM Table_one
WHERE PERIOD_END_DT = (
SELECT MAX(PERIOD_END_DT)
FROM Table_Two
WHERE STATUS = 'NOT ACTIVE'
);

// Assign the value to the variable
LET vYearPeriod = Peek('yyyy-mm', 0, 'PeriodDateTable');

// Drop the temporary table
DROP TABLE PeriodDateTable;

[Mur]

Thank you very much for the solution. I tested the variable in a text box and  it returns the most recent month. However, now that I use it with my set analysis it does not return the correct value. here is my set analysis:

//if(DIM='Loss',num((sum({$< FYPD1={'2024-08','2024-09','2024-10','2024-11','2024-12'}>}Revenue)-
//sum({$< FYPD1={'2024-08','2024-09','2024-10','2024-11','2024-12'}>}DIRECT))

//-sum({$<ACCT_ID={"10*"}, FY={'2024'}, PERIOD={'8','9','10','11','12'}>} CUR_BUD_AMT),'#,##0;(#,##0)'))

and here is how I used the variable:

if(DIMS='Loss',num((sum({$< FYPD1={'2024-08','2024-09','2024-10','2024-11','2024-12'}>}Revenue)-
sum({$< FYPD1={'2024-08','2024-09','2024-10','2024-11','2024-12'}>}DIRECT))

-sum({$< ACCT_ID={"10*"}, FYPD={">=$(VReportPeriod)"}>} CUR_BUD_AMT),'#,##0;(#,##0)'))

note: I combined FY and PERIOD to get FYPD and use it with my variable.

here is how I combined FY and PERIOD in data load editor:

FY + '-' + RIGHT('00' + CONVERT(VARCHAR(6),PERIOD),2) AS FYPD

Please let me know where is the issue with my set analysis or if there is another way to use the variable

thank you!!