using set analysis to find YTD from user selection

[Adam_Wurm]

Hi all,

I was wondering if anyone would be able to help a newcomer out.

I am trying to get results based on FYTD but only for the user selections of Fiscal year and a specific month.

So far my script is pulling data but i think it may be pulling it until today instead of just the end of the selected month.

here is what i am using:

ADMISSION_DT={">=$(=Max(date(Calendar.FinYear)))"},
[Calendar.MONTH]=>}
ADMISSION_ID)

is this correct or am i missing something? 

Thanks in advance.

[vikasmahajan]

Hi ,

You can create FinYearMonth field in load script & use following set analysis to get result:

Sum({$<ADMISSION_DT = {"$(=Max(FinYearMonth))"}>} ADMISSION_ID)

or this

Sum({$<ADMISSION_DT = {"$(=Date(Max(ADMISSION_DT), 'MM/DD/YYYY'))"}>} ADMISSION_ID)   as per your date format.

Thanks

Vikas

[Adam_Wurm]

Thanks for your quick response Vikas.

I think i may been explaining it wrong.

At the moment the original script i posted in the original post is giving me numbers that are close to my numbers i have to validate with.

i believe that even though its pulling the data its not stopping and the end of the month im filtering and its continuing to count until present day.

I have FYTD set in my calendar in the script loader which is my 'Calendar.FinYear' field. 

I am using a FYTD filter pane to select 2022-2023 and a Month filter pane both using the field ADMISSION_DT.

i hope this makes sense.

[Adam_Wurm · Accepted solution]

Thanks to all that looked at this and tried to help. With a little help from my college, we were able to get it working. 

The script i needed is as followed for anyone else that may need this in the future:

Count(
{<
ADMISSION_ID,
[Calendar.MONTH],
ADMISSION_DT={">=$(=Max(date(Calendar.FinYear)))"}
>
*
<
ADMISSION_ID,
[Calendar.MONTH],
ADMISSION_DT={"<=$(=Max(date(ADMISSION_DT)))"}
>
}
ADMISSION_ID)