Convert IF condition into set analysis.

[savitakhyadi]

Hi Everyone,

I am facing a lot of performance issues in my dashboard. I figured it out its because of IF conditions which I am using in the variables.

I am trying to convert these into set analysis but not able to do.

Below are the 2 different expressions which i want to convert into set analysis.

1)

If(v_Summary_KPI='PN',

if(sum(aggr(sum(aggr(max({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>}[Solution WorkEffort TAT Adjusted]),[Activity ID],[HPE Opportunity Id])),[HPE Opportunity Id]))

/Count({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>} DISTINCT [Activity ID])<=10,

num(sum(aggr(sum(aggr(max({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>}[Solution WorkEffort TAT Adjusted]),[Activity ID],[HPE Opportunity Id])),[HPE Opportunity Id]))

/Count({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>} DISTINCT [Activity ID]),'#,##0.0'),

num(sum(aggr(sum(aggr(max({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>}[Solution WorkEffort TAT Adjusted]),[Activity ID],[HPE Opportunity Id])),[HPE Opportunity Id]))

/Count({$<Flag={'Opportunity_Snapshot'},[Snapshot Quarter]={'Q1'},[GBU]={'Advisory and Professional Services','Operational Services'},[Fiscal Quarter]=,[Fiscal Month]=,[Activity Status]={'Completed'},[Presales Sub Type]={'Solution Development'},[Solution WorkEffort TAT Adjusted]-={}>} DISTINCT [Activity ID]),'#,##0')

))

---> This expression I use to calculate all four qtrs,its just I change qtr num here like q1,q2,q3,q4.

--------------------------------------------------------------------------------------

2) This is 2nd expression where I compare q1,q2 data and show the arrows based on value increase/decrease.

If(if(isnull(vSolWorkEffortQ2),0,vSolWorkEffortQ2)>if(isnull(vSolWorkEffortQ1),0,vSolWorkEffortQ1) ,'qmem://<bundled>/BuiltIn/arrow_n.png',

If(if(isnull(vSolWorkEffortQ2),0,vSolWorkEffortQ2)<if(isnull(vSolWorkEffortQ1),0,vSolWorkEffortQ1)  ,'qmem://<bundled>/BuiltIn/arrow_s.png',

If(if(isnull(vSolWorkEffortQ2),0,vSolWorkEffortQ2)=if(isnull(vSolWorkEffortQ1),0,vSolWorkEffortQ1) , 'qmem://<bundled>/BuiltIn/arrow_e.png',

If(if(isnull(vSolWorkEffortQ2),0),' ',

IF(isnull(vSolWorkEffortQ2) , 'qmem://<bundled>/BuiltIn/led.png')))))

but these 2 causing big time slowness to my dashboard.

Can some one please help me to over come this issue.

Many Thanks