Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with Set Analysis

Hi There,

The following expression is used to calculate a value for each month (sPeriod).

We are battling to get the expression to calculate for a previous month(s) using set analysis.

We need to calculate the average of the total of the previous 3 months. If more than one Year/Month (sPeriod) is selected this expression must still hold true.

=sum(aggr(if((sum
(
(((  (
          
if(F_TRNS_AMT <= TOBR_LOW_CASH_VALUE_LEVEL , TOBR_Low_CASH_Factor ,TOBR_Ideal_CASH_Factor )  *     F_CASH_TOBR_Units
     )
     +
    
F_TOBR_Units)  * TOBR_Unit_Reward  )  *  IF ( Accuracy_Amount_pm >= AccF_Step1_FROM  AND   Accuracy_Amount_pm  < AccF_Step2_FROM        ,        AccF_Step1_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step2_FROM  AND   Accuracy_Amount_pm  < AccF_Step3_FROM        ,        AccF_Step2_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step3_FROM  AND   Accuracy_Amount_pm  < AccF_Step4_FROM        ,        AccF_Step3_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step4_FROM  AND   Accuracy_Amount_pm  < AccF_Step5_FROM        ,        AccF_Step4_FACTOR        ,
    
AccF_Step5_FACTOR        )))))
-
(
Accuracy_Amount * AccFactorRATE)

)  ) +

(
sum( -  AccFactorRATE * Accuracy_Amount )) )<0,0,(sum

(
(
(((  (
          
if(F_TRNS_AMT <= TOBR_LOW_CASH_VALUE_LEVEL , TOBR_Low_CASH_Factor ,TOBR_Ideal_CASH_Factor )  *     F_CASH_TOBR_Units
     )
     +
    
F_TOBR_Units)  * TOBR_Unit_Reward  )  *  IF ( Accuracy_Amount_pm >= AccF_Step1_FROM  AND   Accuracy_Amount_pm  < AccF_Step2_FROM        ,        AccF_Step1_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step2_FROM  AND   Accuracy_Amount_pm  < AccF_Step3_FROM        ,        AccF_Step2_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step3_FROM  AND   Accuracy_Amount_pm  < AccF_Step4_FROM        ,        AccF_Step3_FACTOR        ,IF ( Accuracy_Amount_pm >= AccF_Step4_FROM  AND   Accuracy_Amount_pm  < AccF_Step5_FROM        ,        AccF_Step4_FACTOR        ,
    
AccF_Step5_FACTOR        )))))
-
(
Accuracy_Amount * AccFactorRATE)


)) +

(
sum( -  AccFactorRATE * Accuracy_Amount ))))


,
EmpNumber_sPeriod))

Thanks

0 Replies