Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Help in set analysis

Hello got the following expression that is supposed to find by month the number of lost customers

it is counting the distinct customer_key where customer_key is excluded in 12-months-period-back but has invoices in dates prior to the 12-months-back-period with respect to each value in the dimension

SUM( AGGR(count

  (

    distinct

        {

            <

             

  CUSTOMER_KEY=E(

                    {

                                    <

                                        TRANS_TYPE={'INVOICE'}

                                            , MONTH=,YEAR=,QUARTER=//,

                                            TRANS_DATE={"= TRANS_DATE >= $(vTwelve_Months_Back_Selected_Date_II)"}

                                            ,TRANS_DATE={"=TRANS_DATE <= $(vWon_Lost_Max_Date_II)"}

                                         >

                                    }CUSTOMER_KEY)

                   ,CUSTOMER_KEY=P(

                    {

                                    <

                                        TRANS_TYPE={'INVOICE'}

                                            , MONTH=,YEAR=,QUARTER=

                                            ,TRANS_DATE={"=TRANS_DATE< $(vTwelve_Months_Back_Selected_Date_II)"}

                                        >

                                    }IF(CUSTOMER_KEY)

                

              >

            } CUSTOMER_KEY),MONTH_YEAR))

screen_shot.png

However this expression is returning null

it only works if I define the above variables with = sign to return one specific value , however I need that the 12-monts-back period is calculated with respect to each month year of the dimension

gwassenaarrobvoetsmaleksafa

I can walk on water when it freezes
0 Replies