Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi
From Script
Not very helpful man, could at least propose something he could've done in the script to optimize it.
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.
I want to decrease the avg calculation time of the expression, not make it readable
Try to create flags in script!
could you please explain.what do you mean by this
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.