Can these if expressions be replace by set analysis?

[Huiying]

My mission is to improve performance of old dashboards. Currently I have replaced simple if statements by pick(match()). But below if statements are too complicated to me. The ones with range condition, I have no idea how to do. The other ones I have tried to use nested pick(match(), but got different result. Can you please help?

Thank you a million times for your time!

Best regards,

Susan

1)
= Column(5) / Column(2) -- I thought to use sum(Column(5) / Column(2)) to improve performance. But it doesn't work with sum.

2)
= If( %FCOMonthlyReportingTimeId = 'P',
If( GetSelectedCount( Plant_0_Desc ) = 0 AND GetSelectedCount( Plant_4_Desc ) = 0 AND GetSelectedCount( Product_3_Desc ) = 0,
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOVerifiedSavingsTotal'} > } TargetAmount * CurrencyRate ) / 1000,
If( wildmatch( getFieldSelections( Plant_4_Desc ), '*India*' ) > 0,
// All category values are under India\Valves, because they have to be shown when Valves is selected
// If India is selected, 0 is shown
0,
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOVerifiedSavingsCatOrPlant'} > } TargetAmount * CurrencyRate ) / 1000
)
),
pick( match( right( %FCOMonthlyReportingTime, 3 ),
// Month columns
'/01', '/02', '/03', '/04', '/05', '/06', '/07', '/08', '/09', '/10', '/11', '/12',
'YTD',
// Year columns
'014', '015', '016', '017', '018', '019', '020', '021', '022' ),
// Jan - Nov
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 20)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 30)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 40)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 50)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 60)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 70)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 80)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 90)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 100)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 110)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 120)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
// December is next year's January
sum( { < TimeIdForSnapshot_Realized = {$(=FCOMonthlyDashboardYear * 1000 + 1010)}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
// YTD is the minimum of latest snapshot or january of next year
sum( { < TimeIdForSnapshot_Realized = {$(=RangeMin( Max( TimeIdForSnapshot_Realized ), FCOMonthlyDashboardYear * 1000 + 1010 ) ) }, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
// Year columns show next year's January.
sum( { < TimeIdForSnapshot_Realized = {'2015010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2016010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2017010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2018010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2019010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2010010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2021010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2022010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000,
sum( { < TimeIdForSnapshot_Realized = {'2023010'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000
)
)

3)
= If( %FCOMonthlyReportingTimeId = 'P',
If( GetPossibleCount( Plant_0_Desc ) <= 1 OR GetPossibleCount( Product_3_Desc ) <= 1,
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOOTDCatOrPlantPercent'} > } TargetAmount ),
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOOTDTotalPercent'} > } TargetAmount )
),
count( { < %TimeSelection = , StepId = {1001}, OTDIncluded_0_Desc = {"Incl"}, SourceTableName = {"S_PurchaseOrders"}, OTDToConfirmed_Receiving = {"1"} > } OTDToConfirmed_Receiving)
/
count( { < %TimeSelection = , StepId = {1001}, OTDIncluded_0_Desc = {"Incl"}, SourceTableName = {"S_PurchaseOrders"}, OTDToConfirmed_Receiving = {"*"} > } OTDToConfirmed_Receiving)
)

4)
= If( %FCOMonthlyReportingTimeId = 'P',
If( GetPossibleCount( Plant_0_Desc ) <= 1 AND GetPossibleCount( Product_3_Desc ) <= 1,
/* If one plant and one product category is selected */
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOOver14DaysCatAndPlantPercent'} > } TargetAmount ),
If( GetPossibleCount( Plant_0_Desc ) <= 1 OR GetPossibleCount( Product_3_Desc ) <= 1,
/* If one plant or product category is selected */
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOOver14DaysCatOrPlantPercent'} > } TargetAmount ),
sum( { < %TimeSelection =, TargetGroupDesc = {'FCOOver14DaysTotalPercent'} > } TargetAmount )
)
),
If ( Count( { < %TimeSelection = , StepId = {1001}, OTDIncluded_0_Desc = {"Incl"}, SourceTableName = {"S_PurchaseOrders"}, Over14DaysLateToConfirmed_Receiving={"*"} > } Over14DaysLateToConfirmed_Receiving ) > 0,
count( { < %TimeSelection = , StepId = {1001}, OTDIncluded_0_Desc = {"Incl"}, SourceTableName = {"S_PurchaseOrders"}, Over14DaysLateToConfirmed_Receiving={"1"} > } Over14DaysLateToConfirmed_Receiving )
/
count( { < %TimeSelection = , StepId = {1001}, OTDIncluded_0_Desc = {"Incl"}, SourceTableName = {"S_PurchaseOrders"}, Over14DaysLateToConfirmed_Receiving={"*"} > } Over14DaysLateToConfirmed_Receiving )

, 0)
)

5)
= If( %FCOMonthlyReportingTimeId = 'P',
'',
If( Len( %FCOMonthlyReportingTime ) = 7,
// Monthly columns
pick( match( %FCOMonthlyReportingTimeId,
FCOMonthlyDashboardYear * 1000 + 10, FCOMonthlyDashboardYear * 1000 + 20, FCOMonthlyDashboardYear * 1000 + 30, FCOMonthlyDashboardYear * 1000 + 40,
FCOMonthlyDashboardYear * 1000 + 50, FCOMonthlyDashboardYear * 1000 + 60, FCOMonthlyDashboardYear * 1000 + 70, FCOMonthlyDashboardYear * 1000 + 80,
FCOMonthlyDashboardYear * 1000 + 90, FCOMonthlyDashboardYear * 1000 + 100, FCOMonthlyDashboardYear * 1000 + 110, FCOMonthlyDashboardYear * 1000 + 120 ),
count( { < MonthId = {$(=FCOMonthlyDashboardYear * 1000 + 10)}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 20)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 30)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 40)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 50)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 60)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 70)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 80)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 90)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 100)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < MonthId = {">=$(=FCOMonthlyDashboardYear * 1000 + 10)<=$(=FCOMonthlyDashboardYear * 1000 + 110)"}, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc ),
count( { < YearId = { $(=FCOMonthlyDashboardYear) }, %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } total <MonthId> distinct Supplier_0_Desc )
),
// Other columns
count( { < %TimeSelection = , StepId = {29}, SourceTableName = {"S_Purchasing"} > } distinct Supplier_0_Desc )
)
)

[bgerchikov]

Hi Susan,

It looks like that performance problem is caused by big number of expressions in your IF statements. Qlick is calculating all of them, only then it fulfills IF condition. So, instead of 20+ Sum statements, try approach below with passing parameter.

1. Set a parameter variable based on your logic

  exp_YTDValueParameter : '=

pick( match( right( %FCOMonthlyReportingTime, 3 ),
// Month columns
'/01', '/02', '/03', '/04', '/05', '/06', '/07', '/08', '/09', '/10', '/11', '/12',
'YTD',
// Year columns
'014', '015', '016', '017', '018', '019', '020', '021', '022' ),
// Jan - Nov
$(=FCOMonthlyDashboardYear * 1000 + 20),
$(=FCOMonthlyDashboardYear * 1000 + 30),
$(=FCOMonthlyDashboardYear * 1000 + 40),
$(=FCOMonthlyDashboardYear * 1000 + 50),
$(=FCOMonthlyDashboardYear * 1000 + 60),
$(=FCOMonthlyDashboardYear * 1000 + 70),
$(=FCOMonthlyDashboardYear * 1000 + 80),
$(=FCOMonthlyDashboardYear * 1000 + 90),
$(=FCOMonthlyDashboardYear * 1000 + 100),
$(=FCOMonthlyDashboardYear * 1000 + 110),
$(=FCOMonthlyDashboardYear * 1000 + 120),
// December is next year's January
$(=FCOMonthlyDashboardYear * 1000 + 1010),
// YTD is the minimum of latest snapshot or january of next year
$(=RangeMin( Max( TimeIdForSnapshot_Realized ), FCOMonthlyDashboardYear * 1000 + 1010 ) ) ,
// Year columns show next year's January.
'2015010',
'2016010',
'2017010',
'2018010',
'2019010',
'2010010',
'2021010',
'2022010',
'2023010'
)

2. Use this variable in the following way:

= If( %FCOMonthlyReportingTimeId = 'P',
    If( GetSelectedCount( Plant_0_Desc ) = 0 AND GetSelectedCount( Plant_4_Desc ) = 0 AND GetSelectedCount( Product_3_Desc ) = 0,
        sum( { < %TimeSelection =, TargetGroupDesc = {'FCOVerifiedSavingsTotal'} > } TargetAmount * CurrencyRate ) / 1000,
        If( wildmatch( getFieldSelections( Plant_4_Desc ), '*India*' ) > 0,
            0,
                sum( { < %TimeSelection =, TargetGroupDesc = {'FCOVerifiedSavingsCatOrPlant'} > } TargetAmount * CurrencyRate ) / 1000
          )
      ),

sum( { < TimeIdForSnapshot_Realized = { '$(=exp_YTDValueParameter)'}, %TimeSelection = > } TOTAL <MonthId> NetVerifiedSavings_SNAP_Realized * CurrencyRate ) / 1000

)

As you can see, Qlik will execute your statement only once. Try to use this approach on other columns.

Note, I didn't test the syntax.

Good luck!