Replicate BETWEEN in set analysis

divyasavanth — Sat, 16 Nov 2024 01:53:53 GMT

Hi Folks,

Please help me replicate the below SQL into SET analysis

Select COUNT(*) from TableName
where PGM_DESCR = 'PGM1'
and DISTRICT = 'XYZ'
and LAST_DAY(TO_DATE('May-20','Mon-YY')) BETWEEN STATUS_BEG_DATE AND STATUS_END_DATE

Please note May-20 is a Prompt and PGM_DESCR and DISTRICT as well

STATUS_BEG_DATE and STATUS_END_DATE are columns in TableName

I tried this but dint work:

=Count({$<"$=Num(Monthend([Month Filter AP]))"={">=($=Num(Max(STATUS_BEG_DATE))<=($=Num(Max(STATUS_END_DATE))"}>}CASE_ID)

Thanks for the help in advance.

Re: Replicate BETWEEN in set analysis

lironbaram — Wed, 15 Jul 2020 08:38:22 GMT

hi
for starter set analysis works on fields and not expressions
so you'll need to have a date field for this

second assuming there are different STATUS_BEG_DATE AND STATUS_END_DATE for different rows in the table
set analysis isn't your solution
you should use an if statement instead
Count({if(Num(Monthend([Month Filter AP])) >=Num(Max(STATUS_BEG_DATE)) and Num(Monthend([Month Filter AP]))<=Num(Max(STATUS_END_DATE)),CASE_ID))

topic Replicate BETWEEN in set analysis in App Development

Replicate BETWEEN in set analysis

Re: Replicate BETWEEN in set analysis