how to optimize Pick Match

[Anonymous]

How we can optimize below pick match function

Dimension -_List

Expression

=pick(match(_List,'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR'),
SUM( {<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey={$(vMaxDate)},[Issue True Status]={'Addressing','Accepted'},Probability_Flag={'1'}>}Impact_Classification_Value)/1000000,
sum({< CalendarYear=,CalendarMonthName=,CalendarDay=,Link_DateKey=, [Created Date (Issue)]={">=$(vLMDate)<=$(vMaxDate)"},Probability_Flag={'1'}> }Impact_Classification_Value)/1000000,

if(GetSelectedCount(CalendarMonthName)=0 or GetSelectedCount(CalendarMonthName)=1,
sum({<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey=,[Completion Date (Issue)]={">=$(vLMDate)<=$(vMaxDate)"},Probability_Flag={'1'}>} Impact_Classification_Value)/1000000,
sum({<CalendarYear=,CalendarMonthName=,CalendarDay=, Link_DateKey=,[Completion Date (Issue)]={">=$(vMin_DateNum)<=$(vMaxDate)"},Probability_Flag={'1'}>} Impact_Classification_Value))/1000000,
sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"},[Closed Pending Review]={'Yes'},[Issue True Status]={'*'}-{'Closed'}>}Impact_Classification_Value)/1000000,

sum( {<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, [Past Due]={'True'},Probability_Flag={'1'},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"}>} Impact_Classification_Value)/1000000,
sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Probability_Flag={'1'},Link_DateKey={$(vMaxDate)}>}Aggr(Only({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Probability_Flag={'1'},Link_DateKey={$(vMaxDate)}>}Impact_Classification_Value)/1000000,RiskIssueID,_List))-
sum({<CalendarYear={$(vLM_Year)},CalendarMonthName={$(vLM)}, Probability_Flag={'1'},Link_DateKey={$(vLMDate)}>}Aggr(Only({<CalendarYear={$(vLM_Year)},CalendarMonthName={$(vLM)}, Probability_Flag={'1'},Link_DateKey={$(vLMDate)}>}Impact_Classification_Value)/1000000,RiskIssueID,_List)),


sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, DowngradeStatus={'T'},P2GDowngradeStatus={'T'},Probability_Flag={'1'},Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"}>}Impact_Classification_Value)/1000000,

sum({<CalendarYear={$(vCalenderYear)},CalendarMonthName={$(vMonthSelection)}, Link_DateKey={">=$(vMin_DateNum)<=$(vMaxDate)"},DPR={'Yes'},Probability_Flag={'1'}>} Impact_Classification_Value)/1000000)

[el_aprendiz111]

Hi

From Script

[felipedl]

Not very helpful man, could at least propose something he could've done in the script to optimize it.

[felipedl]

Hi shweta,

What do you need with optimizing it?

If you need to control the expressions, so that it's somehow more readable, you could put a variable in between, with a name that states what the variable is doing.

Something like:

Expression

=pick(match(_List,'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR'),
$(sumOfValuesForSpecificReason1),
$(sumOfValuesForSpecificReason2),

$(sumOfValuesForSpecificReason3),

...

And so on.

Felipe.

[Anonymous]

I want to decrease the avg calculation time of the expression, not make it readable

[vinieme12]

Try to create flags in script!

[Anonymous]

could you please explain.what do you mean by this

[Anonymous]

Not an expert.. maybe Pick(Match(Dimension,Value1,Value2,...Value8) takes longer to load in the UI than set analysis:

sum( {$<DimFlag={1}>} Value)

A pre-requisite step would be adding a _ListFlag column to the table with _List values -  'Open','New','Closed','Closed Pending Review','Past Due','Extended Due Date','Full Downgrade','DPR' - which essentially maps each value to 1.