Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am stuck and hoping for some help. This is my function for a count of awards. I am trying to make it so that when a fiscal_year_quarter is selected, values are included for the fiscal year up to and including the selected quarter. The calc_fiscal_year_quarter part of the formula isn't working for me. Do you have any ideas why not? Thanks a bunch.
Count( {<
wr_code-={"*Unknown*", "*Invalid*", "No *"},
group_desc-={"*DD60*", "*DD83*"},
primaryworkroledesc = {"*"},
fiscal_year_quarter=,
calc_fiscal_year_quarter = {" >=$(=(Num(left($(=vFormattedFiscalYearQuarter),4)*10)+1)) <=$(vFormattedFiscalYearQuarter)"}
>}
[award_amount] )
vFormattedFiscalYearQuarter=
Replace( '20' & Mid(GetFieldSelections(fiscal_year_quarter), 3, 2) & Mid(GetFieldSelections(fiscal_year_quarter), 7, 1), ' ', '' )
fiscal_year_quarter, where the selection is made, is in the format "FY24 Q3" and the variable changes it to "20243" which is the format of calc_fiscal_year_quarter
Tip#1: I think you are missing an equal sign here:
Count( {<
wr_code-={"*Unknown*", "*Invalid*", "No *"},
group_desc-={"*DD60*", "*DD83*"},
primaryworkroledesc = {"*"},
fiscal_year_quarter=,
calc_fiscal_year_quarter = {" >=$(=(Num(left($(=vFormattedFiscalYearQuarter),4)*10)+1)) <=$(=vFormattedFiscalYearQuarter)"}
>}
[award_amount] )
Tip#2: When typing your Measure in the Measure box, that a look on the footer of the box. It will display to you a preview of the code you are typing alread rendered.
Tip#3: Try to move the complexity of your Measures to the Load Script. Add extra fields to have your values already formatted or transformed in the way you need it. This will make your Measures easier to do and faster to process.
Regards,
Mark Costa