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

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