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

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
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